Parent Child Relation in same Table

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?

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!

StackExchange Database Administrators Q#194131, answer score: 7