SQL - left join table to itself

Question

In short, want to left join table to itself, but only rows that have id 1 or 2

Table:

id f1
1  a
1  b 
2  a
3  a

expected result

1 2 a a
1 2 b null

does not work:

select t1.id,t2.id, t1.f1,t2.f1
from table t1
left join table t2 on t1.f1 = t2.f1 and t1.id = 1 and t2.id = 2

Any idea ?

Possible duplicate of [Simplest way to do a recursive self-join?](https://stackoverflow.com/questions/1757260/simplest-way-to-do-a-recursive-self-join) — Abu Nooh, Nov 17 '19 at 22:55

score 1 · Accepted Answer · answered Nov 17 '19 at 22:54

This construct is a bit strange, but you need to filter on the first table in where:

select t1.id, t2.id, t1.f1, t2.f1
from table t1 left join
     table t2
     on t1.f1 = t2.f1 and t2.id = 2
where t1.id = 1 ;

The general rule for a left join is that conditions on the first table go in the where clause. Conditions on the second table go in the on clause.

SQL - left join table to itself

1 Answers1