SQL to return parent rows even when no child rows

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

Solution

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