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
country / province / city
Iran / Fars / Shiraz
id = 12321 as a
`fzxit_location` as b
on a.in_location = b.id
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