Sql-server – Parent Child Relation in same Table

hierarchyjoin;sql server

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?

Best Answer

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.

Related Question