Parent Child Relation in same Table

Question

I have a table storing parent/child records as such:

+-------+------------+---------+---------+------------+-----------+
|custid | custname   | deptid  | company |parentcustid| enrolled  |
+=======+============+=========+=========+============+===========+
| 7060  | Sally      |   AB1   | comp1   |  null      |     1     |
| 6953  | Ajit       |   AB7   | comp2   |  7060      |     1     |
| 6957  | Rahul      |   DE1   | comp3   |  7060      |     1     |
| 6958  | uday       |   TG6   | comp4   |  7060      |     1     |
| 6959  | john       |   HY7   | comp5   |  7060      |     1     |
| 6960  | netaji     |   HY5   | comp6   |  7060      |     1     |
| 6961  | prakriti   |   GT6   | comp7   |  7060      |     1     |
| 6962  | sachin     |   KL7   | comp8   |  7060      |     0     |
| 6963  | santosh    |   KK5   | comp9   |  7060      |     1     |
| 6964  | Ravi       |   PP0   | comp10  |  7060      |     1     |
+-------+------------+---------+---------+------------+-----------+

Is it possible to return records where parent record is Enrolled and related Child record is Not Enrolled?

This returns what I need for 1 specific customer:

select a.custid, a.custname, a.deptid, a.company, a.parentcustid, a.enrolled
from customer a
where a.company = 'comp1' and a.Enrolled = 1
union all
select a.custid, a.custname, a.deptid, a.company, a.parentcustid, a.enrolled
from customer a
where a.parentcustid= 7060 and b.Enrolled = 0

+-------+------------+---------+---------+------------+-----------+
|custid | custname   | deptid  | company |parentcustid| enrolled  |
+=======+============+=========+=========+============+===========+
| 7060  | Sally      |   AB1   | comp1   |  null      |     1     |
| 6962  | sachin     |   KL7   | comp8   |  7060      |     0     |
+-------+------------+---------+---------+------------+-----------+

How can I structure the query to return that type of result set for all parent child records in the table?

Answer 1

If you have only one level of children, you could join the tables

SELECT
    a.custid, a.custname, a.deptid, a.company,
    b.custid AS bcustid, b.custname AS bcustname, b.deptid AS bdeptid, b.company AS bcompany
FROM
    customer a
    LEFT JOIN customer b
        ON a.custid = b.parentcustid AND b.Enrolled = 0
WHERE
    a.parentcustid  IS NULL AND a.Enrolled = 1

Like this you don't lose the relation between the parent and the child record.

Since you know that parents are enrolled and children not, returing the enrolled column adds no new information.

---

If you want to keep your original result table shape, use an additional column for sorting

SELECT
    custid, custname, deptid, company, parentcustid, enrolled,
    10 * custid AS orderKey
FROM customer
WHERE parentcustid IS NULL AND Enrolled = 1
UNION ALL
SELECT
    custid, custname, deptid, company, parentcustid, enrolled,
    10 * parentcustid + 1 AS orderKey
FROM customer
WHERE parentcustid IS NOT NULL AND Enrolled = 0
ORDER BY orderKey, custid

Note that this ORDER BY is applied to the whole union, not just to the second SELECT. Like this the children are always listed just below their parents.

Btw., multiplying the key by 2 is enough to get the desired effect for the order key. It just displays nicer if you multiply by 10. If your customer ids always have a maximum of 4 digits, you can also use 10000 * custid for the parents and 10000 * parentcustid + custid for the children and order only by orderKey to get well ordered records.

Answer 2

A common way to do this in SQL Server is to use a Recursive CTE:

Here's your test data as an insert:

CREATE TABLE #yourmom
(
    custid INT,
    custname VARCHAR(10),
    deptid VARCHAR(3),
    company VARCHAR(10),
    parentcustid INT,
    enrolled BIT
)

INSERT #yourmom ( custid, custname, deptid, company, parentcustid, enrolled )
SELECT x.custid, x.custname, x.deptid, x.company, x.parentcustid, x.enrolled
FROM   ( VALUES ( 7060, 'Sally   ', 'AB1', 'comp1 ', NULL, 1 ),
                ( 6953, 'Ajit    ', 'AB7', 'comp2 ', 7060, 1 ),
                ( 6957, 'Rahul   ', 'DE1', 'comp3 ', 7060, 1 ),
                ( 6958, 'uday    ', 'TG6', 'comp4 ', 7060, 1 ),
                ( 6959, 'john    ', 'HY7', 'comp5 ', 7060, 1 ),
                ( 6960, 'netaji  ', 'HY5', 'comp6 ', 7060, 1 ),
                ( 6961, 'prakriti', 'GT6', 'comp7 ', 7060, 1 ),
                ( 6962, 'sachin  ', 'KL7', 'comp8 ', 7060, 0 ),
                ( 6963, 'santosh ', 'KK5', 'comp9 ', 7060, 1 ),
                ( 6964, 'Ravi    ', 'PP0', 'comp10', 7060, 1 )
        ) AS x ( custid, custname, deptid, company, parentcustid, enrolled );

And here's how a Recursive CTE would look for it:

WITH yourdad
AS ( SELECT y.custid, y.custname, y.deptid, y.company, y.parentcustid, y.enrolled
     FROM   #yourmom AS y
     WHERE  y.parentcustid IS NULL
            AND y.enrolled = 1
     UNION ALL
     SELECT ym2.custid, ym2.custname, ym2.deptid, ym2.company, ym2.parentcustid, ym2.enrolled
     FROM   yourdad AS yd
     JOIN   #yourmom AS ym2
     ON ym2.parentcustid = yd.custid
        AND ym2.enrolled = 0 )
SELECT *
FROM   yourdad;

There are some Great Posts® written about them:

• Adam Machanic

• Jeff Moden #1 and #2

• The late great Dwain Camps

Hope this helps!