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.