Display Parent-Child Relationship When Parent and Child Are Stored in Same Table

Display child and parent relationship (if any) in a same table

You can use a self join -- because you want the name of the parent and not the id -- and coalesce() for ordering:

select t.*,
(case when exists (select 1 from t tc where tc.parent = t.id)
then 1 else 0
end)
from t left join
t tp
on t.parent = tp.id
order by coalesce(tp.name, t.name), -- group rows by the parent, if any
(tp.name is null) desc, -- put parent first
t.name; -- order by children

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

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

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.

Finding Parent-Child relationship where levels can be repeated

Reading better your requirements, I think you have to use a recursive query (CTE). In the recursive part it extracts all the hierarchies. I track the Master call in the last column (M2).

This query is not based on time order of calls as my former answer. It works using only CallID and NextCallID, so you can have hierarchy of calls using same time too.

;WITH A AS (SELECT Timestamp,CallID,NextCallID,Type, CallID AS MasterCallID
FROM CALLS WHERE TYPE='M'
UNION ALL
SELECT B.Timestamp,B.CallID,B.NextCallID,B.Type, A.MasterCallID
FROM CALLS B
INNER JOIN A ON A.NEXTCALLID=B.CALLID
WHERE B.TYPE<>'M')
SELECT Timestamp
, MasterCallID
, CallID
, NextCallID
, CASE Type WHEN 'M' Then 'Master' WHEN 'S' THEN 'Sub/Transfer' WHEN 'E' THEN 'End Call' ELSE '' END AS Type
FROM A
ORDER BY TIMESTAMP

Output:

+-------------------------+------------+----------+------------+--------------+
| Timestamp | MASTERCALL | CallID | NextCallID | Type |
+-------------------------+------------+----------+------------+--------------+
| 2017-07-26 14:37:24.000 | 37468024 | 37468024 | 37594497 | Master |
| 2017-07-26 14:51:27.000 | 37468024 | 37594497 | 37595080 | Sub/Transfer |
| 2017-07-26 14:59:30.000 | 37468024 | 37595080 | 37512345 | Sub/Transfer |
| 2017-07-26 14:59:59.000 | 37468024 | 37512345 | NULL | End Call |
| 2017-07-26 18:59:59.000 | 37594497 | 37594497 | NULL | Master |
+-------------------------+------------+----------+------------+--------------+

New version after question edit (above query can create wrong "link" call-mastercall):

; WITH A AS (SELECT Timestamp,CallID,NextCallID,Type, CallID AS MasterCallID, Timestamp AS TIMEMASTER 
FROM CALLS WHERE TYPE='M'
UNION ALL
SELECT B.Timestamp,B.CallID,B.NextCallID,B.Type, A.MasterCallID, A.TIMEMASTER
FROM CALLS B
INNER JOIN A ON B.CALLID=A.NEXTCALLID AND B.TIMESTAMP>A.TIMESTAMP
WHERE B.TYPE<>'M')
SELECT A.Timestamp
, A.MasterCallID
, A.CallID
, A.NextCallID
, CASE A.Type WHEN 'M' Then 'Master' WHEN 'S' THEN 'Sub/Transfer' WHEN 'E' THEN 'End Call' ELSE '' END AS Type
FROM A
INNER JOIN (SELECT TIMESTAMP, CALLID, MAX(TIMEMASTER) MAXTIMEM FROM A GROUP BY TIMESTAMP, CALLID) C ON A.TIMESTAMP=C.TIMESTAMP AND A.CALLID=C.CALLID AND A.TIMEMASTER = C.MAXTIMEM
ORDER BY TIMEMASTER, TIMESTAMP
;

Output with new sample data:

Timestamp                MasterCallID  CallID        NextCallID    Type
------------------------ ------------- ------------- ------------- ------------
2017-07-26 07:08:32.000 37461343 37461343 37565836 Master
2017-07-26 07:35:23.000 37461343 37565836 37565909 Sub/Transfer
2017-07-26 07:42:23.000 37461343 37565909 NULL End Call
2017-07-26 07:31:06.000 37565804 37565804 37565938 Master
2017-07-26 07:45:04.000 37565804 37565938 37566044 Sub/Transfer
2017-07-26 07:52:59.000 37565804 37566044 NULL End Call
2017-07-26 18:14:26.000 37565461 37565461 37565909 Master
2017-07-26 18:29:23.000 37565461 37565909 37566044 Sub/Transfer
2017-07-26 18:33:11.000 37565461 37566044 NULL End Call
2017-07-26 18:24:48.000 37565804 37565804 NULL Master
2017-07-26 18:26:11.000 37565836 37565836 NULL Master
2017-07-26 18:30:06.000 37565938 37565938 NULL Master


Related Topics



Leave a reply



Submit