Re: Advanced SELECT - Mailing list pgsql-sql

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.marketingidAS 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 ''
elseb.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 ansprechpartnerFROM firma fJOIN ( SELECT js.fid, group_concat(js.jobtyp) AS jobtypen FROM (
SELECTDISTINCT 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_fbranchefb 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 (
SELECTDISTINCT 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 (
SELECTDISTINCT 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
GROUPBY au.fid) a ON (f.fid=a.fid)JOIN ( SELECT ms.fid, group_concat(ms.ansprechpartner) AS ansprechpartner FROM (
SELECTDISTINCT 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
fplusfp 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.)