Left outer join involving three tables

Question

From the 3 tables below I need to find 'John', who has a bike but not a car. I'm trying to use this syntax

Select <> from TableA A left join TableB B on A.Key = B.Key where B.Key IS null

so in practise I create a left join from the two tables but i'm bit confused how the where B.Key IS null fits in my query.

select t1.name from 
(table1 t1 join table3 on table3.table1id = t1.id join table2 t2 on table3.table2id = t2.id) 
left join 
(table1 t11 join table3 on table3.table1id = t11.id join table2 t22 on table3.table2id = t22.id) 
on t1.name = t11.name where t2.name = 'Bike' and t22.name = 'Car';

Table1

ID	NAME
1	John
2	Nick

Table2

ID	NAME
1	Bike
2	Car

Table3

table1ID	table2ID
1	1
2	1
2	2

user13964273 · Answer 1 · 2022-12-28T12:47:59.097

If you want your query to run at all cost you can go for something monstrous like this:

select name from table1
left join (select table1Id, table2.id as CarId from table3 join table2 on table2.id = table3.table2Id where table2.name = 'Car') c on id=c.table1Id
left join (select table1Id, table2.id as BikeId from table3 join table2 on table2.id = table3.table2Id where table2.name = 'Bike') b on id=b.table1Id
where CarId is null and BikeId is not null

https://dbfiddle.uk/pbftUJQL

But at the end it is a rough equivalent of straightforward

select name from table1
where not exists (select * from table3 join table2 on table2.id = table3.table2Id where table1.id=table3.table1Id and table2.name = 'Car')
and exists (select * from table3 join table2 on table2.id = table3.table2Id where table1.id=table3.table1Id and table2.name = 'Bike')

You must analyze query plans to choose variant that offer better performance. It may be this one:

select table1.name from table1 join table3 on table1.id=table3.table1Id join table2 on table2.id = table3.table2Id
where table2.name = 'Bike'
and not exists (select * from table3 t3 join table2 t2 on t2.id = t3.table2Id where table1.id=t3.table1Id and t2.name = 'Car')

Salman A · Accepted Answer · 2022-12-28T13:27:09.443

From the 3 tables below I need to find John who has a bike but not a car.

I would rather use this approach, it allows you to include additional criteria (e.g. has a bike and a truck but not a car) by modifying the having clause instead of adding additional joins:

select table1.id, table1.name
from table3
join table1 on table3.table1id = table1.id
join table2 on table3.table2id = table2.id
group by table1.id, table1.name
having count(case when table2.name = 'bike' then 1 end) > 0
and    count(case when table2.name = 'car'  then 1 end) = 0

PS: your original query could be written like this (it was missing aliases and where clause):

select *
from table1
left join (
  table3 as table3_bike join
  table2 as table2_bike on table3_bike.table2id = table2_bike.id
) on table3_bike.table1id = table1.id and
     table2_bike.name = 'bike'
left join (
  table3 as table3_car join
  table2 as table2_car on table3_car.table2id = table2_car.id
) on table3_car.table1id = table1.id and
     table2_car.name = 'car'
where table2_bike.id is not null
and table2_car.id is null

This query has the potential to grow exponentially e.g. if John has two bikes and two cars it'll return 2 x 2 = 4 rows for John. Relational division using exists or having is recommended.

Thanks @salman-a, your alternative solution is cleaner and more easy to read/use/extend indeed. Nevertheless I am curious how I could make my query work too (even if I do not use it in practise) — siwmas, Dec 28 '22 at 12:13
Regarding your comment above... you would left join person table with the many-many table ***twice***, one for cars, one for bikes; that gives you two copies and you just check null and not null inside each copy. The other answer does that although it could be written differently. — Salman A, Dec 28 '22 at 13:06

Left outer join involving three tables

2 Answers2