How can i select all cities belong to certain country with mysql-CodePudding

I have 2 tables

table country - id, name, code, information, capital

table city - id, countrycode, name

How can i show all information from a country and all its cities? capital = id, code=countrycode

So far i have this query, but doesnt work

select country.*, country.capital as cap, city.*
from country
  left join city on country.code = city.countrycode
where code = 'usa'

The script above doesnt show all cities in that country and only shows capitals as number

CodePudding user response：

So not quite sure how your data is set up but you probably want a country table and a city table and the city table has a foreign key to the country table. maybe something like this.

create table Country(ID int, CapitalID int, Name Varchar(255));
create table City(ID int, CountryID int, Name Varchar(255));

here is some sample data

insert into Country( ID, CapitalId, Name) Values (1, 1, 'USA');
insert into City (ID, CountryId, Name) Values (1,1,'Washington DC');
insert into City (ID, CountryId, Name) Values (2,1,'New York');
insert into City (ID, CountryId, Name) Values (3,1,'Seattle');
insert into City (ID, CountryId, Name) Values (4,1,'Denver');

Then just do a join on the foreign key

 select a.Name, b.Name, Case When b.Id = a.CapitalId THEN 'True' Else 'False' End as IsCapital 
    from Country a  join City b on (b.CountryId = a.Id)
where a.Name = 'USA'

result

Name    Name    IsCapital
USA Washington DC   True
USA New York    False
USA Seattle False
USA Denver  False

here is the fiddle https://www.db-fiddle.com/f/szizi97zi9MMWEdA4ijVHX/0

CodePudding user response：

Based on the schema that you provided, just explicitly select the columns that you want like this:

SELECT country.id
     , country.name
     , country.information
     , country.code
     , city.name
FROM country
     LEFT JOIN city ON country.code = city.countrycode
WHERE country.code = 'usa';

If there are no cities in the city table for the country in question, then you'll still get one record with the city.name field as NULL - but I'm assuming that each country has at least its capital as an entry in the city table so that this situation won't arise.