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