| for those concerned ar this topic, a way to do it with 2 LEFT is
select a.value as a,b.value as b1,c11.value as c11 from table_a as a
-- join b
left outer join table_b as b on b.a_pk = a.pk
-- join c avec value = a_1
left join table_c as c11 on c11.b_pk = b.pk and c11.value = 'a_1'
where (
(b.pk is null and c11.pk is null)
OR
(b.pk is not null and c11.pk is not null)
)
But because the clause is on the "WHERE", it will work only with one couple of left. With two or more separated couple, you will fall back to the "nest problem". Because, you will need to put this clause on each 1st level LEFT ( in the "ON" predicate ), which would have to be nest the 2nd left :
select a.value as a,b.value as b1,c11.value as c11 from table_a as a
-- join b
left outer join table_b as b
-- join c avec value = a_1
left join table_c as c11 on c11.b_pk = b.pk and c11.value = 'a_1'
on b.a_pk = a.pk
and (
(b.pk is null and c11.pk is null)
OR
(b.pk is not null and c11.pk is not null)
)
|
