Subquery with multiple rows - Mailing list pgsql-sql

<div class="WordSection1"><p class="MsoNormal"><span lang="EN-US">Hi all!</span><p class="MsoNormal"><span
lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">How can I outsource a subquery?</span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">An Example:</span><p
class="MsoNormal"><spanlang="EN-US">SELECT DISTINCT a.*,</span><p class="MsoNormal"><span lang="EN-US">(       SELECT
SUM(std)</span><p class="MsoNormal"><span lang="EN-US">        FROM all_std </span><p class="MsoNormal"><span
lang="EN-US">        WHERE(a.status <=5 AND status = 5) </span><p class="MsoNormal"><span lang="EN-US">        AND
foerd_idIN (SELECT foerd_id FROM foerds WHERE mass_id = '55896')  </span><p class="MsoNormal"><span lang="EN-US">) AS
done_std</span><pclass="MsoNormal"><span lang="EN-US">(       SELECT SUM(anerk_std) </span><p class="MsoNormal"><span
lang="EN-US">        FROMall_std</span><p class="MsoNormal"><span lang="EN-US">        WHERE (a.status >5 AND status
<5)</span><p class="MsoNormal"><span lang="EN-US">        AND foerd_id IN (SELECT foerd_id FROM foerds WHERE mass_id
='55896')</span><p class="MsoNormal"><span lang="EN-US">) AS accepted_std</span><p class="MsoNormal"><span
lang="EN-US">FROMtable_a a </span><p class="MsoNormal"><span lang="EN-US">WHERE a.mass_id = '55896';</span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">The sub-subquery is “SELECT
foerd_idFROM foerds WHERE mass_id = '55896')” an delivers more than one row.</span><p class="MsoNormal"><span
lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">Now I want to run the subquery only one time. </span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">I tried this: </span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">SELECT DISTINCT a.*,</span><p
class="MsoNormal"><spanlang="EN-US">(       SELECT SUM(std) </span><p class="MsoNormal"><span lang="EN-US">        FROM
all_std</span><p class="MsoNormal"><span lang="EN-US">        WHERE (a.status <=5 AND status = 5) </span><p
class="MsoNormal"><spanlang="EN-US">        AND foerd_id IN (f.foerd_id)   </span><p class="MsoNormal"><span
lang="EN-US">)AS done_std,</span><p class="MsoNormal"><span lang="EN-US">(       SELECT SUM(anerk_std) </span><p
class="MsoNormal"><spanlang="EN-US">        FROM all_std</span><p class="MsoNormal"><span lang="EN-US">        WHERE
(a.status>5 AND status < 5)</span><p class="MsoNormal"><span lang="EN-US">        AND foerd_id IN
(f.foerd_id)</span><pclass="MsoNormal"><span lang="EN-US">) AS accepted_std</span><p class="MsoNormal"><span
lang="EN-US">FROMtable_a a,</span><p class="MsoNormal"><span lang="EN-US">(SELECT foerd_id FROM foerds WHERE mass_id =
'55896') f</span><p class="MsoNormal"><span lang="EN-US">WHERE a.mass_id = '55896';</span><p class="MsoNormal"><span
lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">But the result is not correct because I got one row for
everyrow of the of “f.foerd_id”.</span><p class="MsoNormal"><span lang="EN-US">Allowed is only one row.</span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">How must the SQL looks like to get
theright result?</span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span
lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal">Regards…<pclass="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal"><span
style="font-size:10.0pt;font-family:Consolas;color:#1F497D;mso-fareast-language:DE"> </span><pclass="MsoNormal"><span
style="font-size:10.0pt;font-family:Consolas;color:#1F497D;mso-fareast-language:DE">Mitfreundlichen Grüßen</span><p
class="MsoNormal"><spanstyle="font-size:10.0pt;font-family:Consolas;color:#1F497D;mso-fareast-language:DE">J.
Weiss</span><pclass="MsoNormal"><span
style="font-size:10.0pt;font-family:Consolas;color:#1F497D;mso-fareast-language:DE"> </span><pclass="MsoNormal"><span
style="font-size:10.0pt;font-family:Consolas;color:#1F497D;mso-fareast-language:DE">EntwicklerSachgebiet GEW /
e-Lösungen</span><pclass="MsoNormal"><span
style="font-size:10.0pt;font-family:Consolas;color:#1F497D;mso-fareast-language:DE">E-Mail:<a
href="mailto:j.weiss@dvz-mv.de"><spanstyle="color:blue">j.weiss@dvz-mv.de</span></a></span><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:Consolas;color:#1F497D;mso-fareast-language:DE">Telefon:+49 (3 85) 48 00
351</span><pclass="MsoNormal" style="margin-bottom:12.0pt"><span
style="font-size:10.0pt;font-family:Consolas;color:#1F497D;mso-fareast-language:DE">Telefax:+49 (3 85) 48 00 98 351<br
/>Internet: <a href="http://www.dvz-mv.de/"><span style="color:blue">www.dvz-mv.de</span></a><br /><br /></span><span
style="font-size:7.5pt;font-family:"Arial",sans-serif;color:#1F497D;mso-fareast-language:DE">_____________________________________</span><span
style="color:#1F497D;mso-fareast-language:DE"><br/></span><span
style="font-size:7.5pt;font-family:"Arial",sans-serif;color:#1F497D;mso-fareast-language:DE">DVZ
Datenverarbeitungszentrum<br/> Mecklenburg-Vorpommern GmbH<br /> Lübecker Str. 283 - 19059 Schwerin</span><span
style="color:#1F497D;mso-fareast-language:DE"><br/></span><span
style="font-size:7.5pt;font-family:"Arial",sans-serif;color:#1F497D;mso-fareast-language:DE">Sitzder Gesellschaft:
Schwerin| Eintrag im Handelsregister: HRB 187 / Amtsgericht Schwerin<br /> Geschäftsführer: Hubert Ludwig |
Aufsichtsratsvorsitzender:Staatssekretär Peter Bäumer</span><span style="color:#1F497D;mso-fareast-language:DE"><br
/></span><span
style="font-size:7.5pt;font-family:"Arial",sans-serif;color:#1F497D;mso-fareast-language:DE">_____________________________________</span><span
style="color:#1F497D;mso-fareast-language:DE"></span><pclass="MsoNormal"> </div>