Nested select on sql

Question

I have this structure on database (bad structure indeed)

Country

id_country | desc_country

Region

id_region | id_country | desc_region

City

id_city | id_region | desc_city

Employee

id_city | nom | ape

How I can if is possible recover all employees with their respective descriptions (desc_country, desc_region, desc_city) from id_city of Employee on one sql select.

Looks like a fairly standard normalised structure and you just need to inner join all tables. — Martin Smith, Oct 20 '16 at 20:54
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) — Tyler Roper, Oct 20 '16 at 20:54

score 0 · Answer 1 · answered Oct 20 '16 at 20:55

Looks like you will just need to do a join on each one:

SELECT e.nom, ct.desc_country, r.desc_region, c.desc_city 
FROM Employee AS e
INNER JOIN City as c
  ON e.id_city = c.id_city
INNER JOIN Region as r
  ON c.id_region = r.id_region
INNER JOIN Country as ct
  ON ct.id_country = r.id_country

score 0 · Answer 2 · answered Oct 20 '16 at 21:02

This should work

SELECT cy.id_country, cy.desc_country, r.id_region, r.id_country, r.desc_region, ct.id_city, ct.id_region, ct. desc_city, e.id_city, e.nom, e.ape

FROM Employee AS e
JOIN City AS ct 
ON e.id_city=ct.id_city
JOIN Region AS r
ON ct.id_region=r.id_region
JOIN Country AS cy
ON r.id_country=cy.id_country

Nested select on sql

2 Answers2