How to use nested select

i have the following table structure table "location" has - id - parentLocation_id (relation to itself) - name

let assume we have the following data:

id  parentLocation_id  name

1         null           Egypt
2          1             Cairo
3          2             Zamalek

here we have three levels of locations开发者_C百科 , i want to get all levels (locations) that have id =1 or child of location_id.

The result must have all the three locations if i selected Egypt location (get all inheritance level).

i tried the following

select l.id from Location as l where l.id = 1 or l.id in (select id from Location where parentLocation_id= l.id);

here the result is not correct , and here i reached to the second level only.

How can i do this??

Thanks In Advance

Here is another answer that may fit your desired results better. Note that there are three (3) places that you need to put the desired id

SELECT l1.id AS select_id
FROM location l1
WHERE l1.id IN (
    SELECT l2.id 
    FROM location l2, location l3 
    WHERE l2.id = 1 OR l2.parentLocation_id = 1 OR (l2.parentLocation_id = l3.id AND l3.parentLocation_id = 1)
)

This query works if you always have three levels.

SELECT * 
FROM location l1, location l2, location l3 
WHERE l1.id = 1 
  AND l1.id = l2.parentLocation_id  
  AND l2.id = l3.parentLocation_id

Do you need it to pull out only 2 levels? For example, Egypt, Cairo if it didn't have a 3rd level (Zamalek)?

What you want to store is a heirarchy, and is quite tricky to implement properly in a database.

Have a look at this for more info: Trees and Hierarchies in SQL