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.weiss@dvz-mv.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
_____________________________________