Sid M Sid M - 7 months ago 11
SQL Question

Join between Parent and Child table

I've 2 tables:

Parent
and
Child
with following data

enter image description here

enter image description here

Now when I execute the following queries:

delete from Parent where Id in(2,3,4)
delete from Child


The only record is left
Parent
table

Now when I execute the following query I don't get any records

select p.Id AS [ParentId],p.Name AS [ParentName], c.Id, c.Name from Parent p
Left join Child c on p.Id = c.ParentId
where p.IsActive = 1 and c.IsActive = 1


And when I remove
and c.IsActive = 1
from above query I get the record in Package table, but I want to apply both the active checks. How to achieve this?

Answer

Your where clause turns your left join into an inner join. Use

select p.Id AS [ParentId],p.Name AS [ParentName], c.Id, c.Name 
from Parent p
Left join Child c on p.Id = c.ParentId 
                 and c.IsActive = 1
where p.IsActive = 1 

All filters of the left joined table need to be in the on clause.