SQL LEFT JOIN BEHIND

 

select * from Ramki 

Id Mpin ParentId Status

1 474 0 1

3 100 1 0

4 475 1 1

5 600 0 1

6 700 0 0

Expected Result: Parent , child along with status should active

MpinParent MpinChild

474 475

600 NULL

Correct Query: 

select r1.Mpin as MpinParent,r2.Mpin as MpinChild from Ramki r1 

left join Ramki r2 on r1.Id = r2.ParentId    and  r2.Status = 1   where r1.ParentId = 0    and r1.Status = 1

Try for learn: 

Bug in this: 

select r1.Mpin as MpinParent,r2.Mpin as MpinChild from Ramki r1 

left join Ramki r2 on r1.Id = r2.ParentId    and r1.Status = 1  and  r2.Status = 1   where r1.ParentId = 0