verknüpfte Abfrage über 3 Tabellen ...evtl. mit GROUP_CONCAT ?

[jensengemann]

Hallo,
ich habe 3 Tabellen. in einer stehen Stellenangebote (Vakanzen) z.B. LKW-Fahrer, Bauarbeiter, ...etc.. Diesen Stellenangeboten können beliebig viele Arbeitgeber zugeordnet werden (Tabelle Anwender).

diese Verknüpfung habe ich über eine 3 Tabelle realisiert (Tabelle Zuweisung). In dieser Tabelle steht einfach eine zeilenweise Zuordnung der Vakanzen_id und der anwender_id.


Nun möchte ich die Daten dieser Tabellen so abfragen, dass ich in der Ausgabe die Felder der Tabelle Vankanzen und die dazugehörigen Arbeitgeber in einem Datensatz aufgelistet bekomme.

Mit dem "GROUP_CONCAT" Befehl habe ich zumindest eine kommagetrennte Liste der Arbeitgeber_id's in jedem datensatz einfügen können:

SELECT vakanzen.vakanzen_id, vakanzen.bezeichnung, vakanzen.ort, vakanzen.aktiv, vakanzen.beginnt_am,GROUP_CONCAT(zuweisung.anwender_id) AS arbeitgeber FROM vakanzen JOIN zuweisung ON zuweisung.vakanzen_id = vakanzen.vakanzen_id GROUP BY vakanzen.vakanzen_id ";

Nun möchte ich aber die über group_concat gelisteten ids' (zuweisung.anwender_id) noch mit der Arbeitgebertabelle (Anwender) joinen, so das ich statt den id's die Firmennamen ausgegeben bekomme und vor allem eben auch über die Abfrage nach diesen Firmennamen suchen kann. eben nach Vakanzen denen dem suchstring (anwender.firma) entsprechende Arbeitgeber zugewiesen sind.

Ist sowas überhaupt realisierbar oder ist hier meine grundlegende Herangehensweise falsch?

...ich komme hier mit meine eigenen Recherchen leider absolut nicht weiter, deswegen bedarf es mir wirklich einer hilfreichen Antwort.

Danke und beste Grüße,
Jens

 

[akretschmer]

Natürlich ist das möglich. Du hast 3 Tabellen, joinst aber nur 2. Das, was Du noch suchst, steht in der dritten. Erkennst Du den Weg selber, oder habt ihr grad Nebel? ;-)

Gruß von der Küste. Nebelfrei ;-)

 

[jensengemann]

hmm, ich erkenns leider nicht.

was mir fehlt ist das Wissen wie man die über group_concat separiert gelieferten werte mit einer dritten Tabelle joinen kann. Und dann eben datensätze der ersten Tabelle nach den gejointen werten der dritten abfragen kann.

ein Beispiel würde mir weiterhelfen

Gruß aus dem Dreiländereck

 

[akretschmer]

sinngemäß so:

select a.col1, b.col2, group_concat(c.col3) from table_a a left join table_b b on ... left join table_c c on ... where ... group by ...

also bereits die richtigen String aggregieren, nicht nur die ID's.

 

[akretschmer]

demo:

andreas@[local]:5432/test# create table jobs(id int primary key, name text);
CREATE TABLE
andreas@[local]:5432/test# create table mitarbeiter (id int primary key, name text);
CREATE TABLE
andreas@[local]:5432/test# create table zuweisung(m_id int references mitarbeiter, j_id int references jobs, primary key(m_id, j_id));
CREATE TABLE
andreas@[local]:5432/test# insert into jobs values (1,'fahrer');
INSERT 0 1
andreas@[local]:5432/test# insert into jobs values (2,'gärtner');
INSERT 0 1
andreas@[local]:5432/test# insert into mitarbeiter values (1,'meier');
INSERT 0 1
andreas@[local]:5432/test# insert into zuweisung values (1,1);
INSERT 0 1
andreas@[local]:5432/test# insert into zuweisung values (1,2);
INSERT 0 1
andreas@[local]:5432/test# select m.name, string_agg(j.name,', ') from zuweisung z left join mitarbeiter m on z.m_id=m.id left join jobs j on z.j_id=j.id group by m.name;
 name  |   string_agg   
-------+-----------------
 meier | fahrer, gärtner
(1 row)

andreas@[local]:5432/test#

 

[jensengemann]

select a.col1, b.col2, group_concat(c.col3) from table_a a left join table_b b on ... left join table_c c on ... where ... group by ...

also bereits die richtigen String aggregieren, nicht nur die ID's.

das könnt ich mir vorstellen, dass es funtktioniert. ich joine also die dritte Tabelle mit dem Resultat des joins der zweiten tabelle
muss ich dann mal ausprobieren

mit deiner demo konnt ich leider nix anfangen, weil du dort die Tabelle Zuweisung abfragst.
ich muss eine Tabelle "Vakanzen" abfragen welche ich über eine Tabelle Zuweisung mit einer Tabelle Anwender joinen will. Das Resultat muss dann in etwa so aussehen

Felder:
vakanzen.bezeichnung, vakanzen.ort, vakanzen.beginnt_am, anwender.firma

wobei Anwender.firma eben bel. viele datensätze aus der Tabelle Anwender welche über die Tabelle Zuweisung gejoint worden ist enthalten muss.

aktuell habe ich die Ausgabe (in PHP) so gelöst , dass ich einfach die Tabelle Vakanzen abfrage und beim erstellen der Tabelle mittels "while($row_vakanzen = $result_vakanzen->fetch_assoc()) {…"
bei jedem datensatz eine abfrage der zugewiesenen Arbeitgeber mache (SELECT anwender.firma FROM zuweisung LEFT JOIN anwender ON anwender.anwender_id = zuweisung.anwender_id WHERE zuweisung.vakanzen_id='$vakanzen_id').

Das ist aber eben eine unschöne Lösung, weil ich so auch keine Möglichkeit habe Vakanzen nach zugewiesenen Arbeitgebern zu suchen

 

[akretschmer]

schaue Dir meine Demo noch mal genau an. Was steht im Select-Teil?

 

[jensengemann]

ich möchte die einzelnen werte eines group_concat() results mit einer weiteren Tabelle joinen! So das der group_concat wert nicht "anwender_id1,anwender_id2,anwender_id3,...." (angaben der gejointen tabelle "zuweisung") beinhaltet, sondern "name1,name2, name3,..." (Angaben der Tabelle "anwender", welche ich mit dem group_concat() result joinen möchte)

HMM,

...und ich kann auch nicht die Tabelle "Zuweisung" Abfragen, da es mir ja um die Daten aus der Tabelle Vakanzen geht. In der Tabelle Zuweisung befindet sich lediglich die Zuordnung von Arbeitgeber zu Vakanz.
Weil wenn einer Vakanz kein Arbeitgeber zugwiesen worden ist, steht zu der auch nix in der Tabelle Zuweisung

Also sprich die Datensätze der Tabelle vakanzen sind die abzufragenden Daten.

in dem Bild sieht man, dass einer Vakanz "### Administrator | Dresden | keine Angabe" mehrere Arbeitgeber (über die Tabelle "zuweisung") zugeordnet sind (Engemann IT Consulting, mobiler Admin)

 

[akretschmer]

ich möchte die einzelnen werte eines group_concat() results mit einer weiteren Tabelle joinen! So das der group_concat wert nicht "anwender_id1,anwender_id2,anwender_id3,...." (angaben der gejointen tabelle "zuweisung") beinhaltet, sondern "name1,name2, name3,..." (Angaben der Tabelle "anwender", welche ich mit dem group_concat() result joinen möchte)

schaue Dir mein Beispiel bitte so lange an, bis du erkennst, daß genau das, was Du willst, da passiert.

 

[jensengemann]

ja ok, ich weiß was du meinst. aber dafür muss ich in der Tabelle Zuweisung die Mitarbeiter drin haben. was momentan nur der fall ist, sobald dem ein Job zugewiesen ist.
momentan selecte ich eben die Mitarbeiter ( bei mir Vakanzen) und hol mir über Zuweisung die Jobs (bei mir Arbeitgeber) hinzu.

...aber wenns nicht anders geht werde ich es wohl so machen müssen.
Da muss ich aber beim erstellen eines Mitarbeiters (bei mir Vakanz) einen Eintrag in der Tabelle Mitarbeiter (bei mir Vakanzen) und einen in der Tabelle Zuweisung machen auch wenn dem noch kein Job (bei mir Arbeitgeber) zugewiesen wurde.​

 

[akretschmer]

okay, wenn das so ist ...

test=*# select * from mitarbeiter ;
 id |    name   
----+------------
  1 | meier
  2 | mustermann
(2 Zeilen)

test=*# select * from zuweisung ;
 m_id | j_id
------+------
    1 |    1
    1 |    2
(2 Zeilen)

test=*# select * from jobs ;
 id |  name   
----+---------
  1 | fahrer
  2 | gärtner
(2 Zeilen)

test=*# select m.name, string_agg(j.name,', ') from zuweisung z right join mitarbeiter m on z.m_id=m.id left join jobs j on z.j_id=j.id group by m.name;
    name    |   string_agg   
------------+-----------------
 mustermann |
 meier      | fahrer, gärtner
(2 Zeilen)

test=*#

Finde den Unterschied!

 

[jensengemann]

….jetzt freu mich! DANkE

letzte frage : ist das eine Eigenschaft von Right Join, dass es datensätze aus der gejointen Tabelle listet, obwohl kein eindeutiger Bezug durch die Datensätze der abgefragten Tabelle vorhanden ist?

 

[akretschmer]

Es gibt TONNEN von Doku über die verschiedenen JOIN's. Finde und lese sie!

 

[jensengemann]

ich habe gerade festgestellt, dass die Funktion "string_agg" eine Funktion von PostgreSQL ist. in meinen Fall (mysql) muss ich "group_concat" verwenden.

select m.name, string_agg(j.name,', ') from zuweisung z right join mitarbeiter m on z.m_id=m.id left join jobs j on z.j_id=j.id group by m.name;

ich habe dein Beispiel 1zu1 in meine Umgebung portiert (mitarbeiter -> Vakanzen , jobs->Anwender"). Soweit alles Super,: ich kann nach Einträgen aus der Tabelle "Anwender" suchen und sortieren. allerdings musste ich feststellen, dass es mir einträge aus der Tabelle "Vakanzen", welche in der Tabelle "Zuweisung" nicht vorhanden sind auch nicht ausgibt. was es in deinem Beispiel allerdings tut.

hier meine Anweisung:

"SELECT v.vakanzen_id,v.beginnt_am,v.aktiv,v.ort ,v.bezeichnung, group_concat(a.firma) as zfirma  FROM zuweisung z right join vakanzen v on z.vakanzen_id=v.vakanzen_id left join anwender a on z.anwender_id=a.anwender_id WHERE v.bezeichnung LIKE '%$bezeichnung%' AND v.ort LIKE '%$ort%' AND a.firma LIKE '%$arbeitgeber%'  AND v.status=1 AND v.jobninja LIKE '%$jobninja%' group by v.bezeichnung ORDER BY $sortierung LIMIT $start,$anzahl_eintraege"

 

[akretschmer]

ich habe dein Beispiel 1zu1 in meine Umgebung portiert (mitarbeiter -> Vakanzen , jobs->Anwender"). Soweit alles Super,: ... allerdings musste ich feststellen, ... was es in deinem Beispiel allerdings tut.

Pffft. MySQL verhält sich manchmal halt ... buggy.