Sql Get all children of a parent

Question

Lets say I have an Areas Table ( id, parentId, name, path ).

Given an Id, I'd like to get all childrens (children of children included, recursively) of the given area.

I do store in the path the path from the parents to the child.

Example:

1 NULL New York /1/
2 1    BRONX    /1/2/
3 1    MANH     /1/3/ 
4 3    UpWest   /1/3/4/
5 3    MidEast  /1/3/5/

So when asking for what are the children of New York, the query should return bronx, manh, upwest and mideast. and not only bronx and manh.

Answer 1

Will return all areas that are a child of city with id of 1 (e.g. New York). You can change that number to any other city to return it's children too

select * from areas where path like '%/1/%'

Answer 2

You can use

SELECT * FROM Areas WHERE Path LIKE '%/1/%'

if you have the path stored

Answer 3

Look for START WITH and CONNECT BY in Oracle SQL. this way you can select data with hierarchical relationships (tree-like).

Answer 4

Don't know what database you're using: If SQL Server, Use a Common Table Expression (CTE)

Otherwise,

You need some kind of code or stored procedure.. Using psuedoCode

   Assuming @Parent is Primary key of Area record you want children of...
   --Create Temp table (Does your DB have temp Tables) of Keys 
   --  Say it's called 'Children'
   -- -- make this a temmp table... 
   --  In SQL Server syntax uses a #.
   --  Create Table #Table...  ( or use table variable Declare @Children Table ... ), 
   --  Oracle, MySql have their own syntax... 


   Create Table Children 
   (PK Integer Primary Key Not Null)
   -- -------------------------
   Insert Children(PK)
   Select PK From Area 
   Where Parent = @Parent
   -- -----------------------
   While Exists (Select * From 'Children' As C
                 Where Exists 
                    (Select * From Area
                     Where parent = C.PK
                       And PK Not In
                          (Select PK From 'Children')))
       Begin
           Insert Children(PK)
           Select PK From Area
           Where Parent In (Select PK From Children)
              And PK Not In (Select PK From Children)
       End

   --Then join temp table to Area table and return results

   Select a.* From Area a 
      Join Children C On C.PK = A.PK

Answer 5

Try this:

declare @id int
select @id = 1;

with CustParent (ParentID,ChildID)
      as
      (
       select o.ParentID, o.ChildID
       from Customer o
       where o.ID = @id
       union all
       select cpc.ParentID ,cpc.ID 
       from Customer cpc 
       inner join CustParent cp on cp.ChildID = cpc.ParentID
      ) 

Select Customer.ChildID, Customer.ParentID
from Customer 
inner join CustParent cp on cp.ChildID = Customer.ChildID

I reuse this all the time.

Answer 6

If you have a set number depth that you know you'll never go deeper than this will do what you want:

select * from areas a1
join areas a2 on a1.id = a2.parent
join areas a3 on a2.id = a3.parent
join areas a4 on a3.id = a4.parent
join areas a5 on a4.id = a5.parent
where a1 = 1; --or whatever value you're searching for.

Edit: However, if you already have the path saved (which I didn't notice until now), the path like '%/1/%' is clearly the better solution.

Answer 7

SQLite:

SELECT * FROM Areas where path like (SELECT path || '%' FROM Areas WHERE area="New York")

Answer 8

In MySQL:

SELECT  *
FROM    Areas ap
JOIN    Areas ac
ON      ac.path > ap.path
        AND ac.path < CONCAT(ap.path, ':')
WHERE   ap.id = 1

In PostgreSQL and Oracle:

SELECT  *
FROM    Areas ap
JOIN    Areas ac
ON      ac.path > ap.path
        AND ac.path < ap.path || ':'
WHERE   ap.id = 1

In SQL Server:

SELECT  *
FROM    Areas ap
JOIN    Areas ac
ON      ac.path > ap.path
        AND ac.path < ap.path + ':'
WHERE   ap.id = 1

Unlike LIKE (no pun intended), this will use an index on path.