Mehrere Beziehungen mit Group_Concat()

[karl.kojote]

Hallo,

ich habe eine Tabelle mit Kontakten (Personendaten) und eine mit Telefonnummern und Adressen. Wenn ich eine Abfrage mache, dann kann ich bei nur einer Beziehung mittels Group_Concat() die Beziehungen zusammenführen:

[highlight=sql]select k.name, k.vorname, GROUP_CONCAT(t.telnummer SEPARATOR ', ') from kontakte k
left join kontakte_telefone_link tl on (k.id = tl.kontakte_id)
left join telefone t on (t.id = tl.telefone_id) group by k.name, k.vorname;[/highlight]

Ergebnis-Beispiel:

Mustermann	Hans	08151234, 47110987
Musterfrau	Erna	1234567, 9876541

Bei einer weiteren Beziehung:

[highlight=sql]select k.name, k.vorname, GROUP_CONCAT(t.telnummer SEPARATOR ', '), GROUP_CONCAT(a.stadt SEPARATOR ', ') from kontakte k
left join kontakte_telefone_link tl on (k.id = tl.kontakte_id)
left join telefone t on (t.id = tl.telefone_id)
left join kontakte_adressen_link al on (k.id = al.kontakte_id)
left join Adressen a on (a.id = al.adressen_id) group by k.name, k.vorname;[/highlight]

Ergebnis-Beispiel:

Mustermann	Hans	08151234, 47110987, 08151234, 47110987	Rübenstadt 1, Rübenstadt 1, Rübenstadt 2, Rübenstadt 2
Musterfrau	Erna	1234567, 9876541, 1234567, 9876541	Musterhausen 1, Musterhausen 1, Musterhausen 2, Musterhausen 2

Gewünschtes Ergebnis wäre:

Mustermann	Hans	08151234, 47110987	Rübenstadt 1, Rübenstadt 2
Musterfrau	Erna	1234567, 9876541	Musterhausen 1, Musterhausen 2

Ist das überhaupt möglich, dass die DB intern bei mehreren Beziehungen, diese so zusammenführen und gruppieren kann?


Gruß,
kk

[defo]

Ist das überhaupt möglich, dass die DB intern bei mehreren Beziehungen, diese so zusammenführen und gruppieren kann?

Das ist schon möglich, hängt natürlich vom Statement ab.
Ob group concat das kann, vor allem SQL allgemein, wage ich zu bezweifeln, den Befehl gibt es nicht in jeder DB, weiß nicht ob das ANSI Standard ist.
Was verwendest Du für ein System?

Zur falschen Ausgabe:
Ich sehe hauptsächlich 2 Möglichkeiten, entweder Deine Daten sind nicht ok oder Dein Group Concat funktioniert tatsächlich so nicht (ob das ein Fehler ist oder by Design weiß ich auch nicht).
Im ersten Fall liegt es an Dir, die Daten zu bereinigen für Fall 2 gäbe es wahrscheinlich den Workaround, die beiden Group Concat "nacheinander" auszuführen.
Also mach um Dein erstes Beispiel (Nur Telefonnumern) eine Klammerung und lege das Group Concat für die Stadt in ein äußeres Select. Das sollte gehen.

Es wäre auch möglich, dass der Join im 2. Beispiel falsch aufgebaut ist, aber er sieht ok aus.

[akretschmer]

Ist das überhaupt möglich, dass die DB intern bei mehreren Beziehungen, diese so zusammenführen und gruppieren kann?


Gruß,
kk

Ja,

Du hast:

test=*# select * from kontakte ;
 id |    name
----+------------
  1 | mustermann
  2 | musterfrau
(2 rows)

test=*# select * from telefone ;
 id | nummer
----+---------
  1 | nummer1
  2 | nummer2
  3 | nummer3
  4 | nummer4
(4 rows)

test=*# select * from adressen ;
 id | adresse
----+---------
  1 | adr1
  2 | adr2
  3 | adr3
  4 | adr4
(4 rows)

test=*# select * from kon_tel ;
 k_id | tel_id
------+--------
    1 |      1
    1 |      2
    2 |      3
    2 |      4
(4 rows)

test=*# select * from kon_adr ;
 k_id | a_id
------+------
    1 |    1
    1 |    2
    2 |    3
    2 |    4
(4 rows)

uns willst:

test=*# select distinct k.name, array_to_string(array_agg(t.nummer) over (partition by k.id),','), array_to_string(array_agg(a.adresse) over (partition by k.id),',') from kontakte k left join kon_tel kt on (k.id=kt.k_id) left join telefone t on kt.tel_id=t.id left join kon_adr ka on k.id=ka.a_id left join adressen a on ka.a_id=a.id;
    name    | array_to_string | array_to_string
------------+-----------------+-----------------
 musterfrau | nummer3,nummer4 | adr2,adr2
 mustermann | nummer1,nummer2 | adr1,adr1
(2 rows)

Ich nutze PostgreSQL, mag sein, daß nicht alle DB-Systeme das unterstützen.

[karl.kojote]

Hi,

vielen Dank für die Rückmeldungen. Ich bräuchte es für eine Firebird-DB (weil ich einen embedded-DB brauche). Da wäre es die Funktion "LIST()", aber ich hab auch einen MySQL-Server hier mit dem ich schon mehr gearbeitet habe und da ist es "GROUP_CONCAT()". Bei beiden war das Ergebnis identisch, daher hab ich nur das MySQL-Beispiel gepostet.
Was die Funktionen "array_to_string()" und "array_agg()" betrifft, da weiß ich nicht ob Firebird das kann. Ich mach mich mal schlau.

Zum Vorschlag von defo:

Also mach um Dein erstes Beispiel (Nur Telefonnumern) eine Klammerung und lege das Group Concat für die Stadt in ein äußeres Select. Das sollte gehen.

Ich nix schaffen - Knoten im Kopf. Kannst Du mir das Statement mal erstellen, ich bekomme grad gar nix mehr hin (evtl. hilft ne Mittagspause).


cu
kk

[defo]

Mittagspause ist noch etwas früh oder?

Altes Statement nehmen

[highlight=sql]select k.name, k.vorname, GROUP_CONCAT(t.telnummer SEPARATOR ', ') from kontakte k
left join kontakte_telefone_link tl on (k.id = tl.kontakte_id)
left join telefone t on (t.id = tl.telefone_id) group by k.name, k.vorname[/highlight]

klammern und pudern
[highlight=sql]
Select i.name, i.vorname, i.fon, GROUP_CONCAT(Stadt..) .. from (
select k.id, k.name, k.vorname, GROUP_CONCAT(t.telnummer SEPARATOR ', ') as fon
from kontakte k
left join kontakte_telefone_link tl
on (k.id = tl.kontakte_id)
left join telefone t
on (t.id = tl.telefone_id)
group by k.id, k.name, k.vorname) I,
<plus restlicheTableJoins aus 2. Statement>
[/highlight]

Kriegst Du schon hin

[karl.kojote]

Hi defo,

vielen Dank. Hat funktioniert. Aber ohne Deine Rückmeldung heut mit Sicherheit nicht mehr
Dann schau ich mal, ob Firebird das mit LIST() auch so schön macht (und werde noch kurz Nachberichten).


gracias
kk

[karl.kojote]

So,

auch in Firebird geht's wenn am Schluß nochmal gruppiert wird (braucht LIST() ):

[highlight=sql]
select i.name, i.vorname, i.phon, list(a.stadt || ' - ' || a.strasse, ', ') from
(select k.id, k.name, k.vorname, list(t.telnummer || ' (' || t.kategorie || ')') as phon from
kontakte k left join kontakte_telefone_link tl on (k.id = tl.kontakte_id) inner join telefone t
on (t.id = tl.telefone_id) group by k.id, k.name, k.vorname) i left join kontakte_adressen_link al
on (i.id = al.kontakte_id) inner join adressen a on (a.id = al.adressen_id)
group by i.name, i.storage, i.phon;[/highlight]


Vielen Dank nochmal an defo, der mir "Hirnknoten entwirren" geholfen hat.

Gruß,
kk

[defo]

[OT]

So,

auch in Firebird geht's wenn am Schluß nochmal gruppiert wird (braucht LIST() ):

[highlight=sql]
select i.name, i.vorname, i.phon, list(a.stadt || ' - ' || a.strasse, ', ') from
(select k.id, k.name, k.vorname, list(t.telnummer || ' (' || t.kategorie || ')') as phon from
kontakte k left join kontakte_telefone_link tl on (k.id = tl.kontakte_id) inner join telefone t
on (t.id = tl.telefone_id) group by k.id, k.name, k.vorname) i left join kontakte_adressen_link al
on (i.id = al.kontakte_id) inner join adressen a on (a.id = al.adressen_id)
group by i.name, i.storage, i.phon;[/highlight]


Vielen Dank nochmal an defo, der mir "Hirnknoten entwirren" geholfen hat.

Gruß,
kk

Danke und schön, dass es geholfen hat!


Apropos Hirnknoten, bitte die Ausgabe oben mit der Ausgabe unten vergleichen. Mir fallen da viele Dinge ein, der gordische Knoten bspw., oder Nanuk der Eskimo von Walter Moers "Augen essen mit".

[highlight=SQL]
SELECT i.name, i.vorname, i.phon, list(a.stadt || ' - ' || a.strasse, ', ')
FROM (SELECT k.id, k.name, k.vorname,
list(t.telnummer || ' (' || t.kategorie || ')') AS phon
FROM kontakte k
LEFT JOIN kontakte_telefone_link tl
ON (k.id = tl.kontakte_id)
INNER JOIN telefone t
ON (t.id = tl.telefone_id)
GROUP BY k.id, k.name, k.vorname) i
LEFT JOIN kontakte_adressen_link al
ON (i.id = al.kontakte_id)
INNER JOIN adressen a
ON (a.id = al.adressen_id)
GROUP BY i.name, i.storage, i.phon;
[/highlight]
Vielleicht dankt es einem das Hirn ja, wenn es sich nicht so eine Bleiwüste ansehen muss.
[/OT]

[karl.kojote]

Ich hab's nochmal angepasst für noch weitere Beziehungen:

(und mich auch darum bemüht

Apropos Hirnknoten, bitte die Ausgabe oben mit der Ausgabe unten vergleichen. Mir fallen da viele Dinge ein, der gordische Knoten bspw., oder Nanuk der Eskimo von Walter Moers "Augen essen mit".

und im Notepad++ versucht es schöner zu schreiben )

[highlight=sql]
/*
a kontakt z
b telefon z
c adresse y
e email x
ktl kontakt_telefon_link
kal kontakt_adressen_link
kml kontakt_mail_link
*/
SELECT z.id as "ID", z.name as "Familienname", z.vorname as "Vorname", z.phon as "TelNummern",
y.address as "Adressen", x.mailaddr As "Email-Adressen"
FROM (SELECT a.id, a.name, a.vorname,
list(b.telnummer || ' (' || b.kategorie || ')', '; ') AS phon
FROM kontakt a
LEFT JOIN kontakt_telefon_link ktl
ON (a.id = ktl.kontakt_id)
LEFT JOIN telefon b
ON (b.id = ktl.telefon_id)
GROUP BY a.id, a.name, a.vorname) z,
(SELECT a.id, a.name, a.vorname,
list(c.street || ', ' || c.city, '; ') AS address
FROM kontakt a
LEFT JOIN kontakt_adresse_link kal
ON (a.id = kal.kontakt_id)
LEFT JOIN adresse c
ON (c.id = kal.adresse_id)
GROUP BY a.id, a.name, a.vorname) y,
(SELECT a.id, a.name, a.vorname,
list(e.mailadresse || ' (' || e.kategorie || ')', '; ') AS mailaddr
FROM kontakt a
LEFT JOIN kontakt_mail_link kml
ON (a.id = kml.kontakt_id)
LEFT JOIN mail e
ON (e.id = kml.mail_id)
GROUP BY a.id, a.name, a.vorname) x
where z.id = y.id and z.id = x.id
GROUP BY z.id, z.name, z.vorname, z.phon, y.address, x.mailaddr;
[/highlight]


cu
kk

[defo]

Prima, selten dass jemand seine Ergebnisse aufarbeitet und zur Verfügung stellt!

Ich hab's nochmal angepasst für noch weitere Beziehungen:
(und mich auch darum bemüht
und im Notepad++ versucht es schöner zu schreiben )

Ich will bzw. kann ja niemand zu seinem Glück zwingen, aber statt Notepad gibt es dafür geeignetere Tools, z.B.:
http://www.dpriver.com/pp/sqlformat.htm
oder
http://www.freeformatter.com/sql-for...html#ad-output
oder
natürlich die in den DB IDE integrierten.

Ansonsten, unabhängig von den persönlichen Preferenzen, ist doch gerade in einem Forum keine schlechte Idee den eigenen Code etwas lesbar aufzubereiten statt es einfach so hinzurotzen. Es steigert vermutlich Anzahl der Rückmeldungen und das will man ja vermutlich.