self join issue

From: Ed Rouse <erouse(at)milner(dot)com>
To: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: self join issue
Date: 2015-06-16 20:41:26
Message-ID: DE8D456CF535514BB21272D05C4A1C391CEC7F07@mbx029-e1-va-10.exch029.domain.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Thanks.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message David G. Johnston 2015-06-17 15:18:20 Re: self join issue
Previous Message Andreas Joseph Krogh 2015-06-12 13:47:52 Re: Null principal provided for method...