CODEWITHSUNDEEP
sqlsql-serverdatabaseleft-join
AkhilT
AkhilT

Reputation: 5

Derive Parent Record from Child records in SQL

I have the following Data which look like below table

+----------+-----------+-------------+
| Child_ID | Parent_ID | Identifier  |
+----------+-----------+-------------+
| C1       | p1        | IN          |
| C2       | p1        | OUT         |
| C1       | p2        | IN          |
| C2       | p2        | OUT         |
| C1       | p3        | IN          |
| C2       | p3        | OUT         |
+----------+-----------+-------------+

I need to output the data in such a way that I can display the parent record in a separate row linking the 2 child id based on the identifier.

Desired Result :

+----+-----------+---------+----------+------------+
| ID | Parent_ID | Child_1 | Child_2  | Identifier |
+----+-----------+---------+----------+------------+
| C1 | P1        | NULL    | NULL     | IN         |
| C2 | P1        | NULL    | NULL     | OUT        |
| P1 | NULL      | C1      | C2       | IN         |
| C1 | P2        | NULL    | NULL     | IN         |
| C2 | P2        | NULL    | NULL     | OUT        |
| P2 | NULL      | C1      | C2       | IN         |
+----+-----------+---------+----------+------------+

To achieve This I ran the following query where I tried to left join to separate parent record and then UNION to find child records.

-- Parent 
Select c1.PARENT_ID as ID,
       Parent_Id,
       c1.Child_ID as Child_1
       c2.Child_ID as Child_2
       c1.Identifier
from sampletable as c1
left join sampletable as c2
on c2.PARENT_ID = c1.PARENT_ID 
and c2.Identifier = 'OUT'
where c1.Identifier = 'IN'

UNION

-- CHILD
Select child_id as ID,
       Parent_id,
       CASE when Identifier = 'IN' then Child_ID
       Else NULL END As Child_1,
       CASE when Identifier = 'OUT' then Child_ID
       Else NULL END As Child_2,
       Identifier
from sampletable 
where parent_id is not null

Please can someone point out what am i doing wrong here.

Upvotes: 0

Views: 257

Answers (1)

sticky bit
sticky bit

Reputation: 37472

Select the children as they are.

For the parents use a subquery in the FROM, to get the set of distinct Parent_IDs. Provided, that there are only two children you can use other subqueries selecting min(Child_ID) or max(Child_ID) respectively, in the column list.

UNION ALL both results.

Put an outer query over the result of the UNION ALL and order it by coalesce(Parent_ID, ID), CASE WHEN ID IS NULL THEN 1 ELSE 0 END, ID to achieve that order you want. The CASE is a hack for ensuring, that IDs being NULL come last. (I'm not sure if NULLS come first or last in SQL Server and too lazy to look it up right now. Or there is an database wide option, if I recall correctly? Anyway, having it explicitly in the query is the safest bet.)

SELECT * 
       FROM (SELECT Child_ID ID,
                    Parent_ID,
                    NULL Child_1,
                    NULL Child_2,
                    Identifier
                    FROM sampletable
                    UNION ALL
                    SELECT x.Parent_ID ID,
                           NULL Parent_ID,
                           (SELECT min(Child_ID)
                                  FROM sampletable y
                                  WHERE y.Parent_ID = y.Parent_ID) Child_1,
                           (SELECT max(Child_ID)
                                   FROM sampletable y
                                   WHERE y.Parent_ID = y.Parent_ID) Child_2,
                           'IN' Identifier
                           FROM (SELECT DISTINCT Parent_ID
                                        FROM sampletable) x) u
       ORDER BY coalesce(Parent_ID,
                         ID),
                CASE
                  WHEN ID IS NULL
                    THEN 1
                  ELSE
                    0
                END,
                ID;

SQL Fiddle

Upvotes: 0

Related Questions