What's the best way to get related data from their ID's in a single query?

Question

I have a table where each row has a few fields that have ID's that relate to some other data from some other tables.

Let's say it's called people, and each person has the ID of a city, state and country.

So there will be three more tables, cities, states and countries where each has an ID and a name.

When I'm selecting a person, what's the easiest way to get the names of the city, state and country in a single query?

Note: I know this is possible with joins, however as there are more related tables, the nested joins makes the query hard to read, and I'm wondering if there is a cleaner way. It should also be possible for the person to have those fields empty.

Answer 1

Assuming the following tables:

create table People
(
     ID        int          not null primary key auto_increment
    ,FullName  varchar(255) not null
    ,StateID   int 
    ,CountryID int 
    ,CityID    int 
)
;
create table States
(
     ID   int          not null primary key auto_increment
    ,Name varchar(255) not null
)
;
create table Countries
(
     ID   int          not null primary key auto_increment
    ,Name varchar(255) not null
)
;
create table Cities
(
     ID   int          not null primary key auto_increment
    ,Name varchar(255) not null
)
;

With the Following Data:

insert into Cities(Name) values ('City 1'),('City 2'),('City 3');
insert into States(Name) values ('State 1'),('State 2'),('State 3');
insert into Countries(Name) values ('Country 1'),('Country 2'),('Country 3');
insert into People(FullName,CityID,StateID,CountryID) values ('Has Nothing'   ,null,null,null);
insert into People(FullName,CityID,StateID,CountryID) values ('Has City'      ,   1,null,null);
insert into People(FullName,CityID,StateID,CountryID) values ('Has State'     ,null,   2,null);
insert into People(FullName,CityID,StateID,CountryID) values ('Has Country'   ,null,null,   3);
insert into People(FullName,CityID,StateID,CountryID) values ('Has Everything',   3,   2,   1);

Then this query should give you what you are after.

select 
 P.ID
,P.FullName
,Ci.Name as CityName
,St.Name as StateName
,Co.Name as CountryName
from People P
left Join Cities    Ci on Ci.ID = P.CityID
left Join States    St on St.ID = P.StateID
left Join Countries Co on Co.ID = P.CountryID