Differences

This shows you the differences between two versions of the page.

--- kurs:with_clause [2014/09/10 21:22]
+++ kurs:with_clause [2014/09/10 21:22] (current)
@@ Line 1: / Line 1: @@
+<code>
+with verm_extras as
+(
+SELECT  vrm_name,
+        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, nvl(extras_count, 0) c
+from verm_extras e, (select vrm_name, ext_name, count(ext_name) as extras_count from verm_extras
+                      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>

dokuwiki