SQL to return parent rows even when no child rows

Question

• editing my question *

I have a set of tables. When I filter on the second table, t2, I'd still like to get all rows of t1.

SQL script is below. I feel like I'm getting close while tinkering, but I just can't make it happen.

In short, I need t2's rows when applicable, but all of t1's rows with nulls in the other columns.

Thanks.

create table t1 ( id int identity(1,1), parentName varchar(20) null )
create table t2 ( id int identity(1,1), t1id int not null, childName varchar(20) null )
create table t3 ( id int identity(1,1), t2id int not null, gChildName varchar(20) null )

insert into t1 ( parentName ) values ( 'bob' )
insert into t1 ( parentName ) values ( 'john' )

insert into t2 ( childName, t1id ) values ( 'irving', 1 )
insert into t2 ( childName, t1id ) values ( 'parna', 1 )
insert into t2 ( childName, t1id ) values ( 'mike', 1 )

select
      t1.id,
      t1.parentName,
      t2.id,
      t2.childName
from t1 left outer join t2
      on t2.t1id = t1.id
where t2.childName = 'mike'

-- what i'd LIKE is:
-- 1, bob, 3, mike
-- 2, john, null, null

drop table t3
drop table t2
drop table t1

Answer 1

As others have mentioned, you can move the t3 filter out of the overall WHERE clause and put it into the JOIN, this prevents it from effectively turning your outer join into a pseudo inner join (which happens because none of the NULL values can ever match a WHERE criteria except for IS NULL)

It's a very straightforward change to your sample code - just change WHERE to AND.

create table t1 ( id int identity(1,1), parentName varchar(20) null )
create table t2 ( id int identity(1,1), t1id int not null, childName varchar(20) null )
create table t3 ( id int identity(1,1), t2id int not null, gChildName varchar(20) null )

insert into t1 ( parentName ) values ( 'bob' )
insert into t1 ( parentName ) values ( 'john' )

insert into t2 ( childName, t1id ) values ( 'irving', 1 )
insert into t2 ( childName, t1id ) values ( 'parna', 1 )
insert into t2 ( childName, t1id ) values ( 'mike', 1 )

select
  t1.id,
  t1.parentName,
  t2.id,
  t2.childName

from t1
  left outer join t2 on t2.t1id = t1.id and t2.childName = 'mike'

drop table t3
drop table t2
drop table t1

Answer 2

It sounds like you may be using a left join, but then dropping rows based on your where clause. For example:

Select * from Table1 a
left join Table2 b
on a.ID = b.ID
where b.name like 'A%'

will drop all rows from Table 1 where there is no match in Table 2, even though you left joined (because the where condition is not met when b.name is null).

To avoid this, put your conditions in the join instead, like so:

Select * from Table1 a
left join Table2 b
on a.ID = b.ID and b.name like 'A&'

or add an IsNull to your where clause, like so:

Select * from Table1 a
left join Table2 b
on a.ID = b.ID
where ISNULL(b.name, 'A') like 'A%'

Edit: Now that you have posted your query, here is a specific answer: just change "where" to "and," and it will return the results you have specified.

select
  t1.id,
  t1.parentName,
  t2.id,
  t2.childName
from #t1 t1 left outer join #t2 t2
  on t2.t1id = t1.id 
  and t2.childName = 'mike'

Answer 3

If you are joining 2 or more tables, and want the results from the first, even if there is no match from the second (or third etc), you just need to change the join to a left join. Something like

SELECT * FROM TABLE1 A

LEFT JOIN TABLE2 B ON A.ID=B.RELATEDID

Answer 4

You can simply use a left join:

Select t1.id,t1.name,t2.id id2,t2.name name2,t3.id id3,t3.name name3
From t1 left join
     t2 on t1.id=t2.t1id left join
     t3 on t3.t2id=t2.id
Where your condition here