This shows you the differences between two versions of the page.
kurs:complex_with_clause [2014/09/10 21:22] |
kurs:complex_with_clause [2014/09/10 21:22] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | <code> | ||
+ | with vrm_ext as ( | ||
+ | SELECT tst_vermieter.vrm_name, tst_extras.ext_name | ||
+ | FROM tst_vermieter, tst_fahrzeuge, tst_fhz_ext_int, tst_extras | ||
+ | WHERE ( (tst_vermieter.vrm_id = tst_fahrzeuge.fhz_vrm_id) | ||
+ | AND (tst_fahrzeuge.fhz_id = tst_fhz_ext_int.fei_fhz_id) | ||
+ | AND (tst_extras.ext_id = tst_fhz_ext_int.fei_ext_id) | ||
+ | ) | ||
+ | ) | ||
+ | select distinct e.vrm_name, e.ext_name, ec.count_extras from | ||
+ | vrm_ext e, | ||
+ | (select vrm_name, ext_name, count(ext_name) as count_extras from vrm_ext | ||
+ | group by vrm_name, ext_name) ec | ||
+ | where | ||
+ | ec.vrm_name = e.vrm_name and | ||
+ | ec.ext_name = e.ext_name | ||
+ | order by e.vrm_name | ||
+ | </code> |