Saeid Saeid - 2 years ago 82
SQL Question

please help me to write this mysql query

i have a table location that stores countries , provinces and cities .
i want when search a city by its id i can see its province and country too!
i have tried too much but i really dont know what exact query i need to write! :(

my table name is location and these are my fields with example data:

id enName localName type in_location
1 Iran ایران country 0
2 Fars فارس province 1
3 shiraz شیراز city 2
4 marvdasht مرو دشت city 2

i want when i search id = 3 get this results:

country / province / city

Iran / Fars / Shiraz

how i can write its query? i know i must join the table for 3 times but dont know how exactly do that!!! :(

on of codes i have tried :

in_location ,
id = 12321 as a
`fzxit_location` as b
on a.in_location =

Answer Source

WHERE statment should always come at the end. I think this is what your looking for. You'll be fixed for relationships with 3 children or less though.

SELECT a.enName, b.enName, c.enName FROM location as a
LEFT JOIN location as b ON a.in_location =
LEFT JOIN location as c ON b.in_location =
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download