Re: Subquery with multiple rows

Hi,

Why don't you join table_a and all_std and leave " WHERE (a.status <=5 AND
status = 5 AND foerd_id IN (SELECT foerd_id FROM foerds WHERE mass_id =
'55896') " do fix until got the result you wanted. Sorry not showing you in
detail, just to give idea if it is logic for your solution.

Thanks
On Jun 14, 2016 2:22 PM, "Weiss, Jörg" <J(dot)Weiss(at)dvz-mv(dot)de> wrote:

> Hi all!
>
>
>
> How can I outsource a subquery?
>
>
>
> An Example:
>
> SELECT DISTINCT a.*,
>
> ( SELECT SUM(std)
>
> FROM all_std
>
> WHERE (a.status <=5 AND status = 5)
>
> AND foerd_id IN (SELECT foerd_id FROM foerds WHERE mass_id =
> '55896')
>
> ) AS done_std
>
> ( SELECT SUM(anerk_std)
>
> FROM all_std
>
> WHERE (a.status >5 AND status < 5)
>
> AND foerd_id IN (SELECT foerd_id FROM foerds WHERE mass_id =
> '55896')
>
> ) AS accepted_std
>
> FROM table_a a
>
> WHERE a.mass_id = '55896';
>
>
>
> The sub-subquery is “SELECT foerd_id FROM foerds WHERE mass_id = '55896')”
> an delivers more than one row.
>
>
>
> Now I want to run the subquery only one time.
>
>
>
> I tried this:
>
>
>
> SELECT DISTINCT a.*,
>
> ( SELECT SUM(std)
>
> FROM all_std
>
> WHERE (a.status <=5 AND status = 5)
>
> AND foerd_id IN (f.foerd_id)
>
> ) AS done_std,
>
> ( SELECT SUM(anerk_std)
>
> FROM all_std
>
> WHERE (a.status >5 AND status < 5)
>
> AND foerd_id IN (f.foerd_id)
>
> ) AS accepted_std
>
> FROM table_a a,
>
> (SELECT foerd_id FROM foerds WHERE mass_id = '55896') f
>
> WHERE a.mass_id = '55896';
>
>
>
> But the result is not correct because I got one row for every row of the
> of “f.foerd_id”.
>
> Allowed is only one row.
>
>
>
> How must the SQL looks like to get the right result?
>
>
>
>
>
>
>
>
>
> Regards…
>
>
>
>
>
>
>
> Mit freundlichen Grüßen
>
> J. Weiss
>
>
>
> Entwickler Sachgebiet GEW / e-Lösungen
>
> E-Mail: j(dot)weiss(at)dvz-mv(dot)de
>
> Telefon: +49 (3 85) 48 00 351
>
> Telefax: +49 (3 85) 48 00 98 351
> Internet: www.dvz-mv.de
>
> _____________________________________
> DVZ Datenverarbeitungszentrum
> Mecklenburg-Vorpommern GmbH
> Lübecker Str. 283 - 19059 Schwerin
> Sitz der Gesellschaft: Schwerin | Eintrag im Handelsregister: HRB 187 /
> Amtsgericht Schwerin
> Geschäftsführer: Hubert Ludwig | Aufsichtsratsvorsitzender: Staatssekretär
> Peter Bäumer
> _____________________________________
>
>
>