I have a table of name value pairs like so called test:
fk | name | value
-----------------
1 | A | 1
1 | B | 2
1 | C | 3
1 | D | 4
2 | A | 3
2 | B | 6
2 | C | 1
2 | D | 9
3 | A | 0
3 | B | 3
3 | D | 7
4 | A | 3
4 | B | 3
4 | D | 8
5 | A | 4
5 | B | 5
5 | C | 6
5 | D | 2
6 | A | 3
6 | B | 7
6 | C | 5
6 | D | 8
If I run
select a.fk, a.value as A
from test a
where a.name = 'A'
and fk in (select distinct fk from test)
order by fk
I get 6 rows as expected. If I run
select a.fk, a.value as A, b.value as B
from test a
join test b on (a.fk = b.fk)
where a.name = 'A'
and b.name = 'B'
and a.fk in (select distinct fk from test)
order by a.fk
I also get 6 rows as expected. But if I run
select a.fk, a.value as A, b.value as B, c.value as C, d.value as D
from test a
join test b on (a.fk = b.fk)
join test c on (a.fk = c.fk)
join test d on (a.fk = d.fk)
where a.name = 'A'
and b.name = 'B'
and c.name = 'C'
and d.name = 'D'
and a.fk in (select distinct fk from test)
order by a.fk
I only get 4 rows. The rows for fk 3 and 4 are missing due to those fk values not have the C name.
So I thought using left joins would fix it. However
select a.fk, a.value as A, b.value as B, c.value as C, d.value as D
from test a
left outer join test b on (a.fk = b.fk)
left outer join test c on (a.fk = c.fk)
left outer join test d on (a.fk = d.fk)
where a.name = 'A'
and b.name = 'B'
and c.name = 'C'
and d.name = 'D'
and a.fk in (select distinct fk from test)
order by a.fk
still only returns the same 4 rows as the query above. I have tried various combinations of left and left outer and I still only get 4 rows.
fk | A | B | C | D
1 | 1 | 2 | 3 | 4
2 | 3 | 6 | 1 | 9
5 | 4 | 5 | 6 | 2
6 | 3 | 7 | 5 | 8
Is it possible to return 6 rows from a self joined table in the above case?
fk | A | B | C | D
1 | 1 | 2 | 3 | 4
2 | 3 | 6 | 1 | 9
3 | 0 | 3 | | 7
4 | 3 | 3 | | 8
5 | 4 | 5 | 6 | 2
6 | 3 | 7 | 5 | 8