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

Obviously ignore the table3 here as it's not used; Trying to keep it simpler. Thanks. — ChrisH, Apr 23 '15 at 20:25

CactusCake · Accepted Answer · 2015-04-23T21:21:15.213

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

Right, (not to sound snarky, but of course I'm using outer joins) but when I filter on non-existent t3 rows, I get zero rows back. I need the t1 info and nulls for the rest of the columns. — ChrisH, Apr 23 '15 at 19:51
Ok, well a good way to get an accurate response is to disclose a sample block of code that you have already tried. There was no way to tell that you'd already used an outer join. I have edited my answer to include the right location for your t3 filters. — CactusCake, Apr 23 '15 at 20:04

APH · Answer 2 · 2015-04-23T20:32:38.170

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'

edited Apr 23 '15 at 20:32

answered Apr 23 '15 at 19:37

APH

4,109
1
25
36

Yeah, I think I'm going to have to drop the final criteria from the query (t3.id = 1), and filter that in the client; everything works fine, right up until I start filtering the last table's values. I guess that's to be expected, but I'd love to *ALWAYS* get table1's info. – ChrisH Apr 23 '15 at 19:56
Just put that in the join instead - should get you what you are looking for. e.g. `Select * from t1....left join t3 on t2.ID = t3.t2ID and t3.ID = 1` – APH Apr 23 '15 at 19:59
Edited my initial question to try to make it clearer. Hope this helps. Thanks. – ChrisH Apr 23 '15 at 20:21
Thanks @ChrisH! Edited my answer to include your query. – APH Apr 23 '15 at 20:35
GRAAAAARGGHHH!!! Why the hell didn't I see that umpteen web searches ago??? Thanks! – ChrisH Apr 23 '15 at 20:39
Perfect, I've adapted these changes to my bigger, more complicated actual query and I'm getting the results I want. Thanks again! – ChrisH Apr 23 '15 at 20:43

score 0 · Answer 3 · edited Mar 20 '22 at 02:04

0

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

edited Mar 20 '22 at 02:04

RF1991

2,037
4
8
17

answered Apr 23 '15 at 19:28

Christopher

134
7

score 0 · Answer 4 · answered Apr 23 '15 at 19:32

0

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

answered Apr 23 '15 at 19:32

MauriDev

445
2
8

SQL to return parent rows even when no child rows

4 Answers4