Could someone help me with the following SQL data?
My SQL knowledge is limited and unfortunately I can’t get any further. I want to achieve the following:
Table 1: Country
| CountryID | Name |
|---|---|
| 1 | USA |
| 2 | France |
Table 2:
| CityID | Name |
|---|---|
| 1 | New York |
| 2 | Boston |
| 3 | Paris |
| 4 | Lyon |
Table 3: MapCity
| MapID | CountryID | CityID |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 3 |
| 4 | 2 | 4 |
Desired Result:
| Country.Name | Cities |
|---|---|
| USA | New York, Boston |
| France | Paris, Lyon |
Specifically, it is about how I combine the results in the Cities column.
With the following command I get all the data but one line per city, but I need one line per country and the cities should be merged there.
SELECT * FROM Country JOIN MapCity ON Country.CountryID=MapCity.CountyID JOIN City ON MapCity.City=City.CityID
I would be very grateful for any help!
Thank you!
Hundenase is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.