How to use nested select

Question

Hello,

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 , 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

Answer 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)?

Answer 2

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

Answer 3

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)
)