Обсуждение: Advanced SELECT

Hi Folks,

I hope to have found the right group for my question. I have difficult
sql-task. I try to describe it simple:

We have a table 'company'  with a cid and a table 'sector' with a sid.
They are connected m:n via a third table 'company_sector' which contians
csid, cid and sid.

The normal clause would look like:

SELECT c.companyname, s.sectorname FROM company c, sector s,
company_sector cs WHERE cs.cid = c.cid AND cs.sid = s.sid ORDER BY
c.companyname;

This gives a result looking like this:

c.companyname  | s.sector
---------------+------------------------
company1          | sectora
company1          | sectorb
company2          | sectora
company2          | sectorb
company2          | sectorc
company3          | sectora
company4          | sectorc


instead of this I want to have a listing like:

c.companyname  | ??? (sectors)
---------------+------------------------
company1          | sectora, sectorb
company2          | sectora, sectorb, sectorc
company3          | sectora
company4          | sectorc


But I have no idea, how to write a SELECT-command that gives a listing
like this :( Maybe anyone can help *please*

*Thanks and greetings*
Kai...

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

Unzeitige Genuesse erzeugen Ekel.
(Demokrit, um 460 v. Chr.)

Kai Hessing wrote:
> 
> The normal clause would look like:
> 
> SELECT c.companyname, s.sectorname FROM company c, sector s,
> company_sector cs WHERE cs.cid = c.cid AND cs.sid = s.sid ORDER BY
> c.companyname;

> c.companyname  | ??? (sectors)
> ---------------+------------------------
> company1          | sectora, sectorb
> company2          | sectora, sectorb, sectorc
> company3          | sectora
> company4          | sectorc
> 

Search the mailing-list archives for "custom aggregate concat" and 
you'll quickly find an example of how to write your own custom aggregate 
(like SUM()).

Warning - I don't think you can guarantee the order of elements in the 
aggregated sectors.

--  Richard Huxton  Archonet Ltd

Richard Huxton <dev@archonet.com> writes:
> Search the mailing-list archives for "custom aggregate concat" and 
> you'll quickly find an example of how to write your own custom aggregate 
> (like SUM()).

> Warning - I don't think you can guarantee the order of elements in the 
> aggregated sectors.

In recent PG versions you can.  For example,
select key1, custom_aggregate(detail_field) from(select key1, detail_field from my_table order by key1, key2) ssgroup
bykey1 order by key1;
 

The detail_field values will be fed to the aggregate in order by key2
within each key1 group.  This is one of the examples that motivated
allowing ORDER BY in subselects, even though it's outside the SQL spec.

[ 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.
        regards, tom lane

Richard Huxton schrieb:
> Search the mailing-list archives for "custom aggregate concat" and 
> you'll quickly find an example of how to write your own custom aggregate 
> (like SUM()).
> 
> Warning - I don't think you can guarantee the order of elements in the 
> aggregated sectors.

Thank you very much. This was the right hint where to search. For the
order I'll try to find some possibilities to write a function, that does
a sort on this ;) We'll see...

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

Wer Unrecht tut ist ungluecklicher als wer unrecht leidet.
(Demokrit, um 460 v. Chr.)

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.)