Re: self join issue

On Tuesday, June 16, 2015, Ed Rouse <erouse(at)milner(dot)com> wrote:

> 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
>
>
> ... And (c.name = 'C' or c.name is null) ...

Though I would typically avoid the where clause conditions altogether and
covert each self-join into a query with the where clause embedded.

From test a left join (select FK, count(*) from test where name = 'A' group
by FK) test_a using (FK)

Moreover I would see if I could find a way to avoid the
entity-attribute-value anti-pattern in the first place...

The crosstab function in the tablefunc extension (exact names may vary) may
help if you need to handle something more complicated.

External pivot table implementations doing this kind of thing well...

David J.