Обсуждение: self join issue
<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>
On Tuesday, June 16, 2015, Ed Rouse <erouse@milner.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
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
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
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...
External pivot table implementations doing this kind of thing well...
David J.
<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>