Re: self join issue - Mailing list pgsql-sql

<div style="direction: ltr;font-family: Tahoma;color: #000000;font-size: 10pt;"><br /> > I have a table of name
valuepairs like so called test:<br /> >  <br /> > fk | name | value<br /> > -----------------<br /> > 1 |
A   | 1<br /> > 1 | B    | 2<br /> > 1 | C    | 3<br /> > 1 | D    | 4<br /> > 2 | A    | 3<br /> > 2 |
B   | 6<br /> > 2 | C    | 1<br /> > 2 | D    | 9<br /> > 3 | A    | 0<br /> > 3 | B    | 3<br /> > 3 |
D   | 7<br /> > 4 | A    | 3<br /> > 4 | B    | 3<br /> > 4 | D    | 8<br /> > 5 | A    | 4<br /> > 5 |
B   | 5<br /> > 5 | C    | 6<br /> > 5 | D    | 2<br /> > 6 | A    | 3<br /> > 6 | B    | 7<br /> > 6 |
C   | 5<br /> > 6 | D    | 8<br /> >  <br /> > If I run<br /> >  <br /> > select a.fk, a.value as A<br
/>> from test a<br /> > where a.name = 'A'<br /> > and fk in (select distinct fk from test)<br /> > order
byfk<br /> >  <br /> > I get 6 rows as expected. If I run<br /> >  <br /> > select a.fk, a.value as A,
b.valueas B<br /> > from test a<br /> > join test b on (a.fk = b.fk)<br /> > where a.name = 'A'<br /> > and
b.name= 'B'<br /> > and a.fk in (select distinct fk from test)<br /> > order by a.fk<br /> >  <br /> > I
alsoget 6 rows as expected. But if I run<br /> >  <br /> > select a.fk, a.value as A, b.value as B, c.value as C,
d.valueas D<br /> > from test a<br /> > join test b on (a.fk = b.fk)<br /> > join test c on (a.fk = c.fk)<br
/>> join test d on (a.fk = d.fk)<br /> > where a.name = 'A'<br /> > and b.name = 'B'<br /> > and c.name =
'C'<br/> > and d.name = 'D'<br /> > and a.fk in (select distinct fk from test)<br /> > order by a.fk<br />
> <br /> > I only get 4 rows. The rows for fk 3 and 4 are missing due to those fk values not have the C name.<br
/><br/> hi,<br /> this plan is self explaining and more straightforward<br /> (not tested):<br /><br /><br /> SELECT
fk,<br/>        case when count(a) = 0 then NULL else sum(a) end as a,<br />        case when count(b) = 0 then NULL
elsesum(b) end as b,<br />        case when count(c) = 0 then NULL else sum(c) end as c,<br />        case when
count(d)= 0 then NULL else sum(d) end as d<br /> FROM<br />   (SELECT fk,<br />        case when name='A' then value
endas a,<br />        case when name='B' then value end as b,<br />        case when name='C' then value end as c,<br
/>       case when name='D' then value end as d<br />    FROM yourtable) foo<br /> GROUP BY fk<br />  <br />
regards,<br/> Marc Mamin<br /><br /><br /> >  <br /> > So I thought using left joins would fix it. However<br />
> <br /> > select a.fk, a.value as A, b.value as B, c.value as C, d.value as D<br /> > from test a<br /> >
leftouter join test b on (a.fk = b.fk)<br /> > left outer join test c on (a.fk = c.fk)<br /> > left outer join
testd on (a.fk = d.fk)<br /> > where a.name = 'A'<br /> > and b.name = 'B'<br /> > and c.name = 'C'<br /> >
andd.name = 'D'<br /> > and a.fk in (select distinct fk from test)<br /> > order by a.fk<br /> >  <br /> >
stillonly returns the same 4 rows as the query above. I have tried various combinations of left and left outer and I
stillonly get 4 rows.<br /> > fk | A | B | C | D<br /> > 1  | 1 | 2 | 3 | 4<br /> > 2  | 3 | 6 | 1 | 9<br />
>5  | 4 | 5 | 6 | 2<br /> > 6  | 3 | 7 | 5 | 8<br /> >  <br /> > Is it possible to return 6 rows from a
selfjoined table in the above case?<br /> >  <br /> > fk | A | B | C | D<br /> > 1  | 1 | 2 | 3 | 4<br /> >
2 | 3 | 6 | 1 | 9<br /> > 3  | 0 | 3 |   | 7<br /> > 4  | 3 | 3 |   | 8<br /> > 5  | 4 | 5 | 6 | 2<br /> >
6 | 3 | 7 | 5 | 8<br /> >  <br /> > Thanks.<br /> > <br /></div>