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 |
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.