SQL Query for Parent Child Relationship

query a parent child relationship in sql

Since the WHERE clause is executed after the LEFT JOIN, you can take advantage of that and add a WHERE clause to the query filtering out anything that doesn't have those values:

Select      E.*
From        Event       E
Left Join   Diagnosis   D   On  D.Event_FK = E.Id
Left Join   Observation O   On  O.Event_FK = E.Id
Where       D.Name = 'Cancer'
Or          O.Name = 'Progressive Disease'

Results:

Id  event_type
1   diagnosis
3   observation

Note: Including the right-hand table of a LEFT JOIN in the WHERE clause typically leads to unexpected results as it essentially transforms the LEFT JOIN into an INNER JOIN by filtering out the NULL values. But in this situation, that's exactly what we want to do.

Parent Child Relationship in SQL Server 2019

The below query should work. Although it is subjected to maximum recursion permitted by database which is 100 by default in case of SQL server. So it should work till that many level of nested Parent-Child relationship.

WITH RECURSIVE cte AS (
    SELECT ChildID,ChildName,ParentID,ParentName, 1 AS Level FROM parents

    UNION ALL

    SELECT cte.ChildID,
        cte.ChildName,
        GrandParent.ParentID,
        GrandParent.ParentName,
        Level+1 
    FROM cte 
    INNER JOIN parents AS GrandParent 
    ON cte.ParentID=GrandParent.ChildID 
)
SELECT ChildID,ChildName,ParentID,ParentName 
FROM(
    SELECT cte.*,
        ROW_NUMBER() OVER(PARTITION BY ChildID,ChildName ORDER BY Level DESC) AS rnk 
    FROM cte
) Rank_Highest_Level
WHERE rnk=1
ORDER BY ChildID;

Here is a working example in DB Fiddle.

Recursive query with parent-child relation

Just another option using the data type hierarchyid

There are some additional features and functions associated with hierarchyid

Example

-- Optional See 1st WHERE
Declare @Top int = null  --<<  Sets top of Hier Try 2

;with cteP as (
      Select ID
            ,parent_id 
            ,Name 
            ,HierID = convert(hierarchyid,concat('/',ID,'/'))
      From   YourTable 
      Where  IsNull(@Top,-1) = case when @Top is null then isnull(parent_id ,-1) else ID end
      --Where parent_id is null  -- Use this where if you always want the full hierarchy
      Union  All
      Select ID  = r.ID
            ,parent_id  = r.parent_id 
            ,Name   = r.Name
            ,HierID = convert(hierarchyid,concat(p.HierID.ToString(),r.ID,'/'))
      From   YourTable r
      Join   cteP p on r.parent_id  = p.ID)
Select Lvl   = HierID.GetLevel()
      ,ID
      ,parent_id
      ,Name  
 From cteP A
 Order By A.HierID

Results

Lvl ID  parent_id   Name
1   1   NULL        P1
1   2   NULL        P2
2   3   2           P2-1
2   4   2           P2-2
2   5   2           P2-3
3   6   5           P2-3-1
3   7   5           P2-3-2
1   8   NULL        P3
2   9   8           P3-1

Just for fun, If I set @Top to 2, the results would be

Lvl ID  parent_id   Name
1   2   NULL        P2
2   3   2           P2-1
2   4   2           P2-2
2   5   2           P2-3
3   6   5           P2-3-1
3   7   5           P2-3-2

SQL to retrieve parent-child relationship in parent-child order, from a self referencing table

What about to append an 'order' field? This may be an approach:

WITH ChildLocations(LocationId, FkParentLocationId, [Level]) 
    AS 
    (
        (
            -- Start CTE off by selecting the home location of the user
            SELECT l.LocationId, l.FkParentLocationId, 0 as [Level],
                   cast( str( l.locationId ) as varchar(max) ) as orderField
            FROM   Location l
            WHERE  l.LocationId = @locationId
        )
        UNION ALL 
        -- Recursively add locations that are children ...
        SELECT l2.LocationId, l2.FkParentLocationId, [Level] + 1,
               tmp.orderField + '-' + 
               str(tmp.locationId) as orderField
        FROM   ChildLocations tmp
               INNER JOIN Location l2
                    ON  l2.FkParentLocationId = tmp.LocationId
    )
SELECT * from ChildLocations order by orderField;

Remember than Order by in an Insert is not allowed.

Take a look a sample

SQL Sever parent, child, child relationships within table

You can use a recursive query to fetch all of the related rows. Whether the data will be in the form you want, I don't know, as the question seems a bit unclear about that. But for example

Fetch a record and all descendants:

with r as (
     select *
       from my_table t
      where id = 7
     union all
     select t1.*
       from            my_table t1
            inner join r
                    on t1.parent_id = r.id
)
select * from r;

Fetch a record and all ancestors:

with r as (
     select *
       from my_table t
      where id = 7
     union all
     select t1.*
       from            my_table t1
            inner join r
                    on t1.id = r.parent_id
)
select * from r;

Now maybe you want both children and ancestors. This can get a little trickier; recursion works best in a straight line so there can be no infinite loops. One way is to union together the two above queries. If your real query has complex logic that you don't want to write twice, then you could use this to get a list of ID's and then run the real query over a select ... where id in (my_list) type query.

Another consideration is whether a record can have multiple children. If we have

A
A1 => A
A10 => A1
A11 => A1
A2 => A
A20 => A2
A21 => A2

you could say these are all related (through A; some are "cousins"). So if you search from A1 and union the first two example queries, you'd get A, A1, A10, A11... but would you also want the other children of A? If so you could take a slightly different approach:

First, find the eldest ancestor:

with r as (
     select *
       from my_table t
      where id = 7
     union all
     select t1.*
       from            my_table t1
            inner join r
                    on t1.id = r.parent_id
)
select id from r where parent_id is null;

Then run the original "all descendants" query against that ID. If you want to get it all into a single statement, the following *should * work I think (but I'm not where I can test it):

with ancestors as (
     select *
       from my_table t
      where id = 7
     union all
     select t1.*
       from            my_table t1
            inner join ancestors
                    on t1.id = ancestors.parent_id
) , related as (
     select *
       from ancestors
      where parent_id is null
     union all
     select t1.*
       from            my_table t1
            inner join related
                    on t1.parent_id = related.id
)
select * from related;

How to avoid selection of rows of parents that don't have a child, if they have children in other rows?

maybe use a query like below. this uses exists keyword to check presence of any other not null child

SELECT parent, child
FROM my_table t
WHERE child is NOT NULL
OR NOT EXISTS 
(
SELECT 1 FROM my_table p Where p.parent=t.parent and p.child is Not null
)

---

Related Topics