Re: Advanced SELECT

Hi,

Tom Lane schrieb:
> [ experiments... ] This works reliably in 7.4 and up. Before that,
> the optimizer didn't make the connection between the sort ordering of
> the inner query and that needed by the outer, so it would repeat the
> sort step using only key1 and very possibly destroy the key2 ordering.

*Thanks* this was the same idea, that I had. Combined with a DISTINCT
there are no double entries. It works here perfect with 7.39. If someone
is interested, this is the full SELECT:

-----
SELECT case when lower(substring(f.marketingid,1,1))='d' then 'Deutschland'
when lower(substring(f.marketingid,1,1))='s' then 'Schweiz'
else '' end AS bereich,
f.fid AS fid,
f.marketingid AS marketingid,
f.firmalang AS unternehmen,
case when fp.www IS NULL then '' else fp.www end AS url,
case when fp.jobwww IS NULL then '' else fp.jobwww end AS joburl,
case when b.branchen IS NULL then '' else b.branchen end AS
branchen,
case when j.jobtypen IS NULL then '' else j.jobtypen end AS
gesuchte_jobtypen,
case when g.funktionen IS NULL then '' else g.funktionen end AS
gesuchte_funktionen,
case when a.faecher IS NULL then '' else a.faecher end AS
gesuchte_fachrichtungen,
case when s.zusatz IS NULL then '' else s.zusatz end AS
adresse_zusatz,
case when s.strasse IS NULL then '' else s.strasse end AS strasse,
case when s.plz IS NULL then '' else s.plz end AS plz,
case when s.ort IS NULL then '' else s.ort end AS ort,
case when s.land IS NULL then '' else s.land end AS land,
case when m.ansprechpartner IS NULL then '' else
m.ansprechpartner end AS ansprechpartner
FROM firma f
JOIN (
SELECT js.fid, group_concat(js.jobtyp) AS jobtypen FROM (
SELECT DISTINCT f.fid, jt.jobtyp
FROM firma f LEFT OUTER JOIN (jobtext j JOIN jobtyp jt ON
(j.jobtypid=jt.jobtypid)) ON (f.fid=j.fid)
WHERE (j.status>-1 OR j.status IS NULL) AND (jt.status>-1 OR
jt.status IS NULL) AND f.status>-1 ORDER BY jobtyp
) js GROUP BY js.fid
) j ON (f.fid=j.fid)
JOIN (
SELECT bs.fid, group_concat(bs.fbranche) AS branchen FROM (
SELECT DISTINCT f.fid, b.fbranche
FROM firma f LEFT OUTER JOIN (firma_fbranche fb JOIN fbranche b ON
(fb.fbrancheid=b.fbrancheid)) ON (f.fid=fb.fid)
WHERE (b.status>-1 OR b.status IS NULL) AND f.status>-1 ORDER BY fbranche
) bs GROUP BY bs.fid
) b ON (f.fid=b.fid)
JOIN (
SELECT gs.fid, group_concat(gs.bfunktion) AS funktionen FROM (
SELECT DISTINCT f.fid, b.bfunktion
FROM firma f LEFT OUTER JOIN (firma_bfunktion bf JOIN bfunktion b ON
(bf.bfunktionid=b.bfunktionid)) ON (f.fid=bf.fid)
WHERE (b.status>-1 OR b.status IS NULL) AND f.status>-1 ORDER BY
b.bfunktion
) gs GROUP BY gs.fid
) g ON (f.fid=g.fid)
JOIN (
SELECT au.fid, group_concat(au.fach) AS faecher FROM (
SELECT DISTINCT f.fid, a.fach
FROM firma f LEFT OUTER JOIN (firma_fach fa JOIN fach a ON
(fa.fachid=a.fachid)) ON (f.fid=fa.fid)
WHERE (a.status>-1 OR a.status IS NULL) AND f.status>-1 ORDER BY a.fach
) au GROUP BY au.fid
) a ON (f.fid=a.fid)
JOIN (
SELECT ms.fid, group_concat(ms.ansprechpartner) AS ansprechpartner FROM (
SELECT DISTINCT f.fid,
CASE WHEN (m.titel IS NULL OR length(m.titel)<2) THEN '' ELSE
m.titel || ' ' END
|| CASE WHEN m.vname IS NULL THEN '' ELSE m.vname || ' ' END
|| CASE WHEN m.nname IS NULL THEN '' ELSE m.nname END AS
ansprechpartner
FROM firma f LEFT OUTER JOIN ( sitz s JOIN (SELECT * FROM mitarb
WHERE ffunktionid=1 AND status>-1) m ON (s.sitzid=m.sitzid)) ON
(f.fid=s.fid)
WHERE (s.status>-1 OR s.status IS NULL) AND f.status>-1 ORDER BY
ansprechpartner
) AS ms GROUP BY ms.fid
) m ON (f.fid=m.fid)
LEFT OUTER JOIN (
SELECT f.fid, group_concat(s.zusatz) AS zusatz,
group_concat(s.strasse) AS strasse, group_concat(s.plz) AS plz,
group_concat(s.ort) AS ort, group_concat(l.land) AS land
FROM firma f JOIN sitz s ON (f.fid=s.fid) JOIN land l ON
s.landid=l.landid
WHERE s.status>-1 AND s.hauptsitz=1 AND f.status>-1 GROUP BY f.fid
) s ON (f.fid=s.fid)
LEFT OUTER JOIN fplus fp ON (f.fid=fp.fid)
ORDER BY lower(substring(f.marketingid,1,1)), lower(f.firmalang);
------

*greets*
Kai

--
GnuPG-PublicKey -> http://www.hobsons.de/pgp/kai_hessing.asc

Gut ist nicht Nichtfreveln, sondern nicht einmal freveln wollen.
(Demokrit, um 460 v. Chr.)