self join issue - Mailing list pgsql-sql
| From | Ed Rouse |
|---|---|
| Subject | self join issue |
| Date | |
| Msg-id | DE8D456CF535514BB21272D05C4A1C391CEC7F07@mbx029-e1-va-10.exch029.domain.local Whole thread Raw |
| Responses |
Re: self join issue
("David G. Johnston" <david.g.johnston@gmail.com>)
Re: self join issue (Marc Mamin <M.Mamin@intershop.de>) |
| 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>