[SQL] 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

JOINS are the only way to really do this.

You might be able to change your schema, but the problem will be the same regardless.

(A City is always in a State, which is always in a Country - so the Person could just have a reference to the city_id rather than all three. You still need to join the 3 tables though).

Answer 2

Joins are the answer. With practise they will become more readable to you.
There may be special cases where creating a function would help you, for example you could do the following (in Oracle, I don't know any mysql):
You could create a function to return a formatted address given the city state and country codes, then your query becomes

SELECT first_name, last_name, formated_address(city_id, state_id, country_id) 
FROM people
WHERE some_where_clause;

where formated_address does individual lookups on the city state and country tables and puts separators between the decoded values, or returns "no address" if they are all empty, etc

Answer 3

If the tables involved are reference tables (i.e. they hold lookup data that isn't going to change during the life time of a session), depending on the nature of your application, you could pre-load the reference data during you application start up. Then your query doesn't need to do the joins, instead it returns the id values, and in your application you do a decode of the ids when you need to display the data.

Answer 4

The easiest solution is to use the names as the primary keys in city, state, and country. Then your person table can reference them by the name instead of the pseudokey "id". That way, you don't need to do joins, since your person table already has the needed values.

It does take more space to store a string instead of a 4-byte pseudokey. But you may find the tradeoff worthwhile, if you are threatened by joins as much as you seem to be (which, by the way, is like a PHP programmer being reluctant to use foreach -- joins are fundamental to SQL in the same way).

Also there are many city names that appear in more than one state. So your city table should reference the state table and use these two columns as the primary key.

CREATE TABLE cities (
  city_name VARCHAR(30),
  state     CHAR(2),
  PRIMARY KEY (city_name, state),
  FOREIGN KEY (state) REFERENCES states(state)
);

CREATE TABLE persons (
  person_id    SERIAL PRIMARY KEY,
  ...other columns...
  city_name    VARCHAR(30),
  state        CHAR(2),
  country_name VARCHAR(30),
  FOREIGN KEY (city_name, state) REFERENCES cities(city_name, state),
  FOREIGN KEY (country_name) REFERENCES countries(country_name)
);

This just an example of the technique. Of course it's more complex than this, because you may have city names in more than one country, you may have countries with no states, and so on. The point is SQL doesn't force you to use integer pseudokeys, so use CHAR and VARCHAR keys where appropriate.

Answer 5

There is no cleaner way than joins. If the fields are allowed to be empty, use outer joins

SELECT c.*, s.name AS state_name
  FROM customer c
  LEFT OUTER JOIN state s ON s.id = c.state
 WHERE c.id = 10

Answer 6

A disadvantage of standard SQL is the the return data needs to be in tabular format. However some database vendors have added features that makes it possible to select data in non-tabular format. I don't know whether MySQL knows such features.

Answer 7

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

Answer 8

According to the description of the schema that you have given you will have to use JOINS in a single query.

SELECT 
   p.first_name 
 , p.last_name
 , c.name as city
 , s.name as state
 , co.name as country 
FROM people p 
LEFT OUTER JOIN city c 
  ON p.city_id = c.id
LEFT OUTER JOIN state s 
  ON p.state_id = s.id
LEFT OUTER JOIN country co
  ON p.country_id = co.id;

The LEFT OUTER JOIN will allow you to fetch details of person even if some IDs are blank or empty.

Another way is to redesign your lookup tables. A city is always in a state and a state in a country. Hence your city table will have columns : Id, Name and state_id. Your state table will be : Id, Name and country_id. And country table will remain the same : Id and Name.

The person table will now have only 1 id : city_id

Now your query will be :

 SELECT 
   p.first_name 
 , p.last_name
 , c.name as city
 , s.name as state
 , co.name as country 
FROM people p 
LEFT OUTER JOIN city c 
  ON p.city_id = c.id
LEFT OUTER JOIN state s 
  ON c.state_id = s.id
LEFT OUTER JOIN country co
  ON s.country_id = co.id;

Notice the difference in the last two OUTER JOINS

Answer 9

All great answers but the questioner specified they didn't want to use joins. As one respondent demonstrated, assuming your Cities, States, and Countries tables have an Id and a Description field you might be able to do something like this:

SELECT
     p.Name, c.Description, s.Description, ct.Description
FROM
     People p, Cities c, States s, Countries ct
WHERE
     p.Id = value AND
     c.Id = value AND
     s.Id = value AND
     ct.Id = value;

Answer 10

Create a view that does the Person, City, State, and Country joins for you. Then just reference the View in all other joins.

Something like:

CREATE VIEW FullPerson AS  
SELECT Person.*, City.Name, State.Name, Country.Name  
FROM  
  Person LEFT OUTER JOIN City ON Person.CityId = City.Id  
  LEFT OUTER JOIN State ON Person.StateId = State.Id  
  LEFT OUTER JOIN Country ON Person.CountryId = Country.Id

Then in other queries, you can

SELECT FullPerson.*, Other.Value  
FROM FullPerson LEFT OUTER JOIN Other ON FullPerson.OtherId = Other.Id