Select All Parents or Children in Same Table Relation SQL Server

Select all parents or children in same table relation SQL Server

I have met this problem，I resolved problem by this way

 --all  "parent + grandparent + etc" @childID Replaced with the ID you need

with tbParent as
(
   select * from Elem where [KEY]=@childID
   union all
   select Elem.* from Elem  join tbParent  on Elem.[KEY]=tbParent.PARENT_KEY
)
 SELECT * FROM  tbParent
 --all "sons + grandsons + etc" @parentID Replaced with the ID you need

with tbsons as
(
  select * from Elem where [KEY]=@parentID
  union all
  select Elem.* from Elem  join tbsons  on Elem.PARENT_KEY=tbsons.[KEY]
)
SELECT * FROM tbsons

PS.My English is not good.

How to find Parents and Children in a same table

You can achieve this by below query: This query display all the child with it's parent name. This results in multiple rows.

SELECT child.id
    ,child.NAME
    ,parent.NAME
FROM categories AS parent
LEFT JOIN categories AS child ON child.parentid = parent.Id

Order by can be this way, but didn't tested

SELECT child.id
    ,child.NAME
    ,parent.NAME
FROM categories AS parent
LEFT JOIN categories AS child ON child.parentid = parent.Id
ORDER BY coalesce(parent.id, child.id)
    ,parent.id IS NOT NULL
    ,child.id

Recursive Parent/Child in same table query in SQL where parent is PK

This produces the results you are asking for:

with cte as (
      select idchild, idparent,
             convert(varchar(max), idchild) as children
      from family f
      where not exists (select 1 from family f2 where f2.idparent = f.idchild)
      union all
      select f.idchild, f.idparent,
             concat(f.idchild, ',', cte.children)
      from cte join
           family f
           on cte.idparent = f.idchild
     )
 select *
 from cte
 order by idchild;

Here is the SQL Fiddle.

Get all data of parent child relation-ship from same table in mysql

if you're just looking for it's parent,grandparent,greatgrand parent you can use something like this.

SELECT id,title,parent_id FROM
    (SELECT id,title,parent_id,
       CASE WHEN id = 1209 THEN @id := parent_id
            WHEN id = @id THEN @id := parent_id
            END as checkId
     FROM Test
     ORDER BY id DESC) as T
WHERE checkId IS NOT NULL

sqlfiddle

And just in case if you wanted to find all children, and grand children or great grand children of an id you can use this

SELECT id,title,parent_id FROM
    (SELECT id,title,parent_id,
            CASE WHEN id = 1200 THEN @idlist := CONCAT(id)
                 WHEN FIND_IN_SET(parent_id,@idlist) THEN @idlist := CONCAT(@idlist,',',id)
            END as checkId
     FROM Test
     ORDER BY id ASC) as T
WHERE checkId IS NOT NULL

sqlfiddle for finding children

query for finding all parents/grandparents/greatgrandparents of multiple children

SELECT id,title,parent_id FROM
(SELECT id,title,parent_id,
       CASE WHEN id in (1209,1206) THEN @idlist := CONCAT(IFNULL(@idlist,''),',',parent_id)
            WHEN FIND_IN_SET(id,@idlist) THEN @idlist := CONCAT(@idlist,',',parent_id)
            END as checkId
FROM Test
ORDER BY id DESC)T
WHERE checkId IS NOT NULL

sqlfiddle

How to get all children of parent item when all are in the same row and table

You can use a Recursive CTE:

DECLARE @pID VARCHAR(20) = 'A'

;WITH CTE AS (
   SELECT ChildPart
   FROM mytable
   WHERE Part = @pID

   UNION ALL

   SELECT t1.ChildPart
   FROM mytable AS t1
   INNER JOIN CTE AS t2 ON t1.Part = t2.ChildPart
)
SELECT ChildPart
FROM CTE

select Parent Child Rows in same Table

SELECT
  *
FROM
  yourTable
WHERE
     parent != 0
  OR EXISTS (SELECT *
               FROM yourTable children
              WHERE chilren.parent = yourTable.id
            )

The first condition checks if the row points to a parent, and the second condition checks if the row has any children.

---

Related Topics