Get all countries states and cities in one query

Question

I have three tables with the following data:

countries
+-----+----------+
| id  |   name   |
+-----+----------+
| 1   | country1 |
| 7   | country2 |
+-----+----------+

states
+-----+----------+------------+
| id  |   name   | country_id |
+-----+----------+------------+
| 3   | state1   |     1      |
| 9   | state2   |     7      |
| 11  | state3   |     1      |
| 17  | state4   |     1      |
+-----+----------+------------+

cities
+-----+----------+------------+
| id  |   name   |  state_id  |
+-----+----------+------------+
| 5   | city1    |     3      |
| 6   | city2    |     9      |
| 22  | city3    |     9      |
| 24  | city4    |     17     |
| 25  | city5    |     11     |
| 26  | city6    |     11     |
+-----+----------+------------+

I’m trying to select all data so that I can generate the following output:

+-----+---------------------------+--------+-------+
| id  |   table_name   | country  | state  |  city |
+-----+---------------------------+--------+-------+
| 1   |    countries   | country1 |        |       |
| 3   |      states    | country1 | state1 |       |
| 5   |      cities    | country1 | state1 | city1 |
| 11  |      states    | country1 | state3 |       |
| 25  |      cities    | country1 | state3 | city5 |
| 26  |      cities    | country1 | state3 | city6 |
| 17  |      states    | country1 | state4 |       |
| 24  |      cities    | country1 | state4 | city4 |
| 7   |    countries   | country2 |        |       |
| 9   |      states    | country2 | state2 |       |
| 5   |      cities    | country2 | state2 | city2 |
| 5   |      cities    | country2 | state2 | city3 |
+-----+---------------------------+--------+-------+

I know it’s challenging, but I was wondering if that is possible to generate such a result with a SELECT or can it only be done programmatically? Thanks!

No you can't use union, union requires common number of columns in all the tables — Jim Macaulay, Apr 27 '17 at 11:25
Possible duplicate of [How can I join multiple SQL tables using the IDs?](http://stackoverflow.com/questions/9853586/how-can-i-join-multiple-sql-tables-using-the-ids) — Mantas Čekanauskas, Apr 27 '17 at 11:52

score 2 · Accepted Answer · answered Apr 27 '17 at 11:27

You need these 3 SQL statements:

All Cities:

SELECT cit.id, 'cities', cont.name, st.name, cit.name 
FROM countries cont 
INNER JOIN states st ON  cont.id = st.country_id 
INNER JOIN  join cities cit ON st.id = cit.state_id

All states:

SELECT stat.id, 'states', cont.name, st.name, '' 
FROM  countries cont 
INNER JOIN states st ON cont.id = st.country_id

All countries;

SELECT cont.id, 'countries', cont.name, '', '' FROM countries cont

Then you can combine them all like

SELECT cit.id, 'cities', cont.name, st.name, cit.name 
FROM countries cont 
INNER JOIN states st ON  cont.id = st.country_id 
INNER JOIN  join cities cit ON st.id = cit.state_id

  UNION ALL

SELECT stat.id, 'states', cont.name, st.name, '' 
FROM  countries cont 
INNER JOIN states st ON cont.id = st.country_id

  UNION ALL

SELECT cont.id, 'countries', cont.name, '', '' FROM countries cont

score 0 · Answer 2 · answered Apr 27 '17 at 11:24

0

You can use below query

SELECT  C.id,  C.name AS table_name, 'country1' AS country, S.name AS state, CI.city 
FROM COUNTRIES C
FULL OUTER JOIN STATES S
ON (C.ID = S.ID)
FULL OUTER JOIN CITIES CI
ON (C.ID=CI.ID);

Or you can use

SELECT  C.id,  C.name AS table_name, 'country1' AS country, S.name AS state, CI.city 
FROM COUNTRIES C, STATES S, CITIES CI
WHERE C.ID = (+)S.ID AND
C.ID=(+)CI.ID;

answered Apr 27 '17 at 11:24

Jim Macaulay

4,709
4
28
53

I don't think MySQL supports full outer join. – jarlh Apr 27 '17 at 11:27
(+)S.ID is an invalid construct. As a matter of interest which flavour of sql supports this? – P.Salmon Apr 27 '17 at 11:34

score 0 · Answer 3 · answered Apr 27 '17 at 11:58

I'd written this before I saw @nos had already supplied a virtually identical answer. This version adds field aliases, sorts the data as per the OP's sample output and avoids a typo.

select
    c.id,
    'countries' as table_name,
    c.name as country,
    '' as state,
    '' as city
from countries c
union
select
    s.id,
    'states' as table_name,
    c.name as country,
    s.name as state,
    '' as city
from countries c
JOIN states s ON c.id = s.country_id
union
select
    ci.id,
    'cities' as table_name,
    c.name as country,
    s.name as state,
    ci.name as city
from countries c
JOIN states s ON c.id = s.country_id
JOIN cities ci ON s.id = ci.state_id
order by country, state, city

Get all countries states and cities in one query

3 Answers3