Re: self join issue

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

hi,
this plan is self explaining and more straightforward
(not tested):

SELECT fk,
case when count(a) = 0 then NULL else sum(a) end as a,
case when count(b) = 0 then NULL else sum(b) end as b,
case when count(c) = 0 then NULL else sum(c) end as c,
case when count(d) = 0 then NULL else sum(d) end as d
FROM
(SELECT fk,
case when name='A' then value end as a,
case when name='B' then value end as b,
case when name='C' then value end as c,
case when name='D' then value end as d
FROM yourtable) foo
GROUP BY fk

regards,
Marc Mamin

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