Sql Get All Parent Children

Question

Suppose I have a realm table (id, parentId, name, path).

Given Id, I would like all the children (children of the children to be included, recursively) from this area.

I keep the path from 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/

Therefore, when you ask what the children of New York are, the request should return the Bronx, Manh, in the West and the Middle East. and not only the Bronx and Manh.

Answer 1

Will return all areas that are affiliated to the city with identifier 1 (for example, New York). You can change this number to any other city to return it to children.

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

Answer 2

you can use

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

,

Answer 3

, , , , :

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.

: , ( ), , "%/1/%", .

Answer 4

MySQL:

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

PostgreSQL Oracle:

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

SQL Server:

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

LIKE ( ), path.

Answer 5

START WITH CONNECT BY Oracle SQL. ().

Answer 6

, : SQL Server, (CTE)

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

Try the following:

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 use it all the time.

Answer 8

SQLite:

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