self join issue - Mailing list pgsql-sql

<div class="WordSection1"><p class="MsoNormal"><span style="font-family:"Courier New"">I have a table of name value
pairslike so called test:</span><p class="MsoNormal"><span style="font-family:"Courier New""> </span><p
class="MsoNormal"><spanstyle="font-family:"Courier New"">fk | name | value</span><p class="MsoNormal"><span
style="font-family:"CourierNew"">-----------------</span><p class="MsoNormal"><span style="font-family:"Courier New"">1
|A    | 1</span><p class="MsoNormal"><span style="font-family:"Courier New"">1 | B    | 2</span><p
class="MsoNormal"><spanstyle="font-family:"Courier New"">1 | C    | 3</span><p class="MsoNormal"><span
style="font-family:"CourierNew"">1 | D    | 4</span><p class="MsoNormal"><span style="font-family:"Courier New"">2 |
A   | 3</span><p class="MsoNormal"><span style="font-family:"Courier New"">2 | B    | 6</span><p
class="MsoNormal"><spanstyle="font-family:"Courier New"">2 | C    | 1</span><p class="MsoNormal"><span
style="font-family:"CourierNew"">2 | D    | 9</span><p class="MsoNormal"><span style="font-family:"Courier New"">3 |
A   | 0</span><p class="MsoNormal"><span style="font-family:"Courier New"">3 | B    | 3</span><p
class="MsoNormal"><spanstyle="font-family:"Courier New"">3 | D    | 7</span><p class="MsoNormal"><span
style="font-family:"CourierNew"">4 | A    | 3</span><p class="MsoNormal"><span style="font-family:"Courier New"">4 |
B   | 3</span><p class="MsoNormal"><span style="font-family:"Courier New"">4 | D    | 8</span><p
class="MsoNormal"><spanstyle="font-family:"Courier New"">5 | A    | 4</span><p class="MsoNormal"><span
style="font-family:"CourierNew"">5 | B    | 5</span><p class="MsoNormal"><span style="font-family:"Courier New"">5 |
C   | 6</span><p class="MsoNormal"><span style="font-family:"Courier New"">5 | D    | 2</span><p
class="MsoNormal"><spanstyle="font-family:"Courier New"">6 | A    | 3</span><p class="MsoNormal"><span
style="font-family:"CourierNew"">6 | B    | 7</span><p class="MsoNormal"><span style="font-family:"Courier New"">6 |
C   | 5</span><p class="MsoNormal"><span style="font-family:"Courier New"">6 | D    | 8</span><p
class="MsoNormal"><spanstyle="font-family:"Courier New""> </span><p class="MsoNormal"><span style="font-family:"Courier
New"">IfI run </span><p class="MsoNormal"><span style="font-family:"Courier New""> </span><p class="MsoNormal"><span
style="font-family:"CourierNew"">select a.fk, a.value as A</span><p class="MsoNormal"><span style="font-family:"Courier
New"">fromtest a</span><p class="MsoNormal"><span style="font-family:"Courier New"">where a.name = 'A'</span><p
class="MsoNormal"><spanstyle="font-family:"Courier New"">and fk in (select distinct fk from test)</span><p
class="MsoNormal"><spanstyle="font-family:"Courier New"">order by fk</span><p class="MsoNormal"><span
style="font-family:"CourierNew""> </span><p class="MsoNormal"><span style="font-family:"Courier New"">I get 6 rows as
expected.If I run</span><p class="MsoNormal"><span style="font-family:"Courier New""> </span><p class="MsoNormal"><span
style="font-family:"CourierNew"">select a.fk, a.value as A, b.value as B</span><p class="MsoNormal"><span
style="font-family:"CourierNew"">from test a</span><p class="MsoNormal"><span style="font-family:"Courier New"">join
testb on (a.fk = b.fk)</span><p class="MsoNormal"><span style="font-family:"Courier New"">where a.name = 'A'</span><p
class="MsoNormal"><spanstyle="font-family:"Courier New"">and b.name = 'B'</span><p class="MsoNormal"><span
style="font-family:"CourierNew"">and a.fk in (select distinct fk from test)</span><p class="MsoNormal"><span
style="font-family:"CourierNew"">order by a.fk</span><p class="MsoNormal"><span style="font-family:"Courier
New""> </span><pclass="MsoNormal"><span style="font-family:"Courier New"">I also get 6 rows as expected. But if I
run</span><pclass="MsoNormal"><span style="font-family:"Courier New""> </span><p class="MsoNormal"><span
style="font-family:"CourierNew"">select a.fk, a.value as A, b.value as B, c.value as C, d.value as D</span><p
class="MsoNormal"><spanstyle="font-family:"Courier New"">from test a</span><p class="MsoNormal"><span
style="font-family:"CourierNew"">join test b on (a.fk = b.fk)</span><p class="MsoNormal"><span
style="font-family:"CourierNew"">join test c on (a.fk = c.fk)</span><p class="MsoNormal"><span
style="font-family:"CourierNew"">join test d on (a.fk = d.fk)</span><p class="MsoNormal"><span
style="font-family:"CourierNew"">where a.name = 'A'</span><p class="MsoNormal"><span style="font-family:"Courier
New"">andb.name = 'B'</span><p class="MsoNormal"><span style="font-family:"Courier New"">and c.name = 'C'</span><p
class="MsoNormal"><spanstyle="font-family:"Courier New"">and d.name = 'D'</span><p class="MsoNormal"><span
style="font-family:"CourierNew"">and a.fk in (select distinct fk from test)</span><p class="MsoNormal"><span
style="font-family:"CourierNew"">order by a.fk</span><p class="MsoNormal"><span style="font-family:"Courier
New""> </span><pclass="MsoNormal"><span style="font-family:"Courier New"">I only get 4 rows. The rows for fk 3 and 4
aremissing due to those fk values not have the C name.</span><p class="MsoNormal"><span style="font-family:"Courier
New""> </span><pclass="MsoNormal"><span style="font-family:"Courier New"">So I thought using left joins would fix it.
However</span><pclass="MsoNormal"><span style="font-family:"Courier New""> </span><p class="MsoNormal"><span
style="font-family:"CourierNew"">select a.fk, a.value as A, b.value as B, c.value as C, d.value as D</span><p
class="MsoNormal"><spanstyle="font-family:"Courier New"">from test a</span><p class="MsoNormal"><span
style="font-family:"CourierNew"">left outer join test b on (a.fk = b.fk)</span><p class="MsoNormal"><span
style="font-family:"CourierNew"">left outer join test c on (a.fk = c.fk)</span><p class="MsoNormal"><span
style="font-family:"CourierNew"">left outer join test d on (a.fk = d.fk)</span><p class="MsoNormal"><span
style="font-family:"CourierNew"">where a.name = 'A'</span><p class="MsoNormal"><span style="font-family:"Courier
New"">andb.name = 'B'</span><p class="MsoNormal"><span style="font-family:"Courier New"">and c.name = 'C'</span><p
class="MsoNormal"><spanstyle="font-family:"Courier New"">and d.name = 'D'</span><p class="MsoNormal"><span
style="font-family:"CourierNew"">and a.fk in (select distinct fk from test)</span><p class="MsoNormal"><span
style="font-family:"CourierNew"">order by a.fk</span><p class="MsoNormal"><span style="font-family:"Courier
New""> </span><pclass="MsoNormal"><span style="font-family:"Courier New"">still only returns the same 4 rows as the
queryabove. I have tried various combinations of left and left outer and I still only get 4 rows.</span><p
class="MsoNormal"><spanstyle="font-family:"Courier New"">fk | A | B | C | D</span><p class="MsoNormal"><span
style="font-family:"CourierNew"">1  | 1 | 2 | 3 | 4</span><p class="MsoNormal"><span style="font-family:"Courier
New"">2 | 3 | 6 | 1 | 9</span><p class="MsoNormal"><span style="font-family:"Courier New"">5  | 4 | 5 | 6 | 2</span><p
class="MsoNormal"><spanstyle="font-family:"Courier New"">6  | 3 | 7 | 5 | 8</span><p class="MsoNormal"><span
style="font-family:"CourierNew""> </span><p class="MsoNormal"><span style="font-family:"Courier New"">Is it possible to
return6 rows from a self joined table in the above case?</span><p class="MsoNormal"><span style="font-family:"Courier
New""> </span><pclass="MsoNormal"><span style="font-family:"Courier New"">fk | A | B | C | D</span><p
class="MsoNormal"><spanstyle="font-family:"Courier New"">1  | 1 | 2 | 3 | 4</span><p class="MsoNormal"><span
style="font-family:"CourierNew"">2  | 3 | 6 | 1 | 9</span><p class="MsoNormal"><span style="font-family:"Courier
New"">3 | 0 | 3 |   | 7</span><p class="MsoNormal"><span style="font-family:"Courier New"">4  | 3 | 3 |   | 8</span><p
class="MsoNormal"><spanstyle="font-family:"Courier New"">5  | 4 | 5 | 6 | 2</span><p class="MsoNormal"><span
style="font-family:"CourierNew"">6  | 3 | 7 | 5 | 8</span><p class="MsoNormal"><span style="font-family:"Courier
New""> </span><pclass="MsoNormal"><span style="font-family:"Courier New"">Thanks.</span></div>