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
Oracle: SQL Query That Returns Rows with Only Numeric Values
Postgresql Query to Count/Group by Day and Display Days with No Data
SQL Server Equivalent of MySQL's Now()
Add Row to Query Result Using Select
Fastest Way to Export Blobs from Table into Individual Files
How to Use Order by with Union All in SQL
How to Clear Oracle Execution Plan Cache for Benchmarking
How to Analyse a SQLite Query Execution
Why Should I Capitalize My SQL Keywords
How to Do a Simple 'Find and Replace" in Mssql
Calling a Stored Procedure in Oracle with in and Out Parameters
Exporting Blob from MySQL Database to File with Only SQL
Postgresql Calculate Difference Between Rows
SQL - Stored Procedure with Select Statement Using in (@Variable_Commadelimitedlistofids)
Avoiding Concurrency Problems with Max+1 Integer in SQL Server 2008... Making Own Identity Value