Saeid Saeid - 6 months ago 11
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 :

SELECT
in_location ,
enName
FROM
location
WHERE
id = 12321 as a
INNER JOIN
SELECT
*
FROM
`fzxit_location` as b
on a.in_location = b.id

Answer

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 = b.id
LEFT JOIN location as c ON b.in_location = c.id
WHERE a.id = 3