Problem mit JOIN über 3 Tabellen

[dom77]

Hallo zusammen,

hab schon ne Weile gegooglt, aber mein Problem nur zur Hälfte lösen können.

Ich habe folgende 3 Tabellen:

jobs (Aufträge)
users (Benutzer)
correctors2jobs (Korrekteure der Aufträge)

Wenn ich mir die Spalten von "jobs" für eine Listen-Ausgabe geben lasse, habe ich es bereits geschafft, dass die dort vorkommende "job_user_id" nach dem Klarnamen des Nutzers in "users" sucht und das Ergebnis als "job_uploader" ausgibt:

        "SELECT job_id,
                       job_in_use,
                       job_initialized,
                       job_active,
                       job_user_id,
                       job_number,
                       job_name,
                       job_description,
                       job_path,
                       job_start,
                       job_edit,
                       job_status,
                       job_idml_filepath,
                       job_idml_timestamp,
                       job_language,
                       job_module,
                       CONCAT(u.user_prename,' ',u.user_name) AS job_uploader
                       FROM jobs
                       JOIN users u ON u.user_id = job_user_id
                       WHERE job_user_id = :user_id AND job_module = :job_module
                       ORDER BY job_id ASC"

Jetzt gibt es aber noch die Tabelle "correctors2jobs", wo man UserIDs für weitere Tätigkeiten am Job findet, die der "job_id" zugeordnet sind. An diese IDs kann ich auf ähnliche Weise wie oben kommen, jedoch will ich auch hier die Klarnamen haben. Sozusagen wie beim "job_uploader" nur mit dem Umweg über eine Zwischentabelle.

Ich habe schon einiges Unqualifiziertes ausprobiert und bevor ihr Euch die Haare rauft, frage ich direkt nach.

Danke für Eure Hilfe,
Dom

[defo]

ohne dass die Spalten genau angegeben sind und die Beziehung selbst, würde ich einfach mal pauschal sagen, dass Du:
-die correctors für sich ebenfalls mit den Users joinen musst (für die Klarnamen) und
-dieses Teilergebnis musst Du über die mir unbekannte Relation ebenfalls zum Job joinen

Im finalen Statement taucht dann wohl die Usertable 2 mal auf, weil sie für unterschiedliche Mengen zur Aufbereitung dient.

Als "Trick" kannst Du Dir auch für beide Mengen jeweils einen View definieren, der nichts anderes tut, als die Namen aufzulösen. Diese Views kannst Du dann überall bequem wiederverwenden, ohne den Kram jedes Mal mit einzubauen.

[dom77]

Hallo Defo,

mir ist gerade selbst aufgefallen, dass ich noch zu wenig geposted habe. Ich hole das mal hier nach:

die Spalten von jobs kann man ja in o.g. Statement sehen

users -> user_id -> INT
users -> user_prename -> STRING
users -> user_name -> STRING

correctors2jobs -> corr_id -> INT
correctors2jobs -> corr_job_id -> INT identisch zu job_id von jobs
correctors2jobs -> corr_user_id -> INT identisch zu user_id von users

das wars eigentlich. Es kann in "correctors2jobs" mehrere Zeilen zu einem Job geben

Vielleicht kannst Du mit den Angaben ein wenig detailierter auf die Lösung eingehen, denn zunächst hab ich erstmal nur Bahnhof verstanden. Ich programmiere zwar schon länger in PHP, aber mySQL habe ich leider immer sehr stiefmütterlich behandelt, da ich meistens auf XML-Daten zurückgreifen musste.

Danke und Gruss
Dominik

[defo]

Es kann in "correctors2jobs" mehrere Zeilen zu einem Job geben

Und du willst, dass alles zusammen ausgegeben wird, also ein Select das Mehrfachnennung von Jobs und jeweiligen correctors liefert ?

[dom77]

Hallo,

ja genau, wobei es völlig i.O. wäre, wenn die Namen der Einzelnen "correctors2jobs" als ein String zusammengefasst würden.

Gute N8 und bis morgen Forum,
Dom

[defo]

Wie wärs wenn Du erstmal ein Statement erstellst, dass die Controlers mit Usernamen darstellt?
Sicher nicht schwer oder?

[dom77]

Moin,

Du meinst wohl das Folgende. Zur Erklärung, ich habe im Endeffekt Kontrolleure und Freigebende in zwei (dritten) Tabellen und die Benennung oben hatte ich der Einfachheit willens angepasst. Hier meine original Statements:

$sql2 = "SELECT corrector_corrector_id,
                        corrector_job_id
                        CONCAT(u.user_prename,' ',u.user_name) AS corrector_fullname
                        FROM correctors
                        JOIN users u ON u.user_id = corrector_corrector_id
                        WHERE corrector_job_id = :job_id AND job_module = :job_module
                        ORDER BY job_id ASC";
        $sql3 = "SELECT correction_validator_correction_validator_id,
                        correction_validator_job_id
                        CONCAT(u.user_prename,' ',u.user_name) AS corrector_fullname
                        FROM correction_validators
                        JOIN users u ON u.user_id = correction_validator_correction_validator_id
                        WHERE correction_validator_job_id = :job_id AND job_module = :job_module
                        ORDER BY job_id ASC";

Danke für Deine Hilfe
LG
Dom

[defo]

Du meinst wohl das Folgende. Zur Erklärung, ich habe im Endeffekt Kontrolleure und Freigebende in zwei (dritten) Tabellen und die Benennung oben hatte ich der Einfachheit willens angepasst. Hier meine original Statements:

Ok, jetzt nimmst Du eines der Statements
setzt es in Klammern und
fügst es im Statement des Eingangspostings als weiteren join ein.
Die join condition sollte klar sein, von job.job_id auf das jeweilige Referenz-Feld aus stmt 1 oder 2.
Where und Order By kannst Du weglassen bzw. nach Bedarf im Gesamtstatement anpassen

Wenn es mit einem der Statement funktioniert, tut es das auch mit dem anderen oder mit beiden, wenn es gebraucht wird.

Der Jointyp muss ggF. variert werden, wenn nicht für jeden Job ein corrector oder validator vorhanden ist! Aber das kann man ganz am Ende machen, wenn nötig.

[dom77]

Hallo nochmal,

habe das Statement nun ohne Errors einfügen können, aber ich bekomme keine Ausgabe (0 Datensätze) auch wenn ich mit den JOINS spiele. Magst Du nochmal einen Blick drauf werfen:

$sql = "SELECT job_id,
                       job_in_use,
                       job_initialized,
                       job_active,
                       job_user_id,
                       job_number,
                       job_name,
                       job_description,
                       job_path,
                       job_start,
                       job_edit,
                       job_status,
                       job_idml_filepath,
                       job_idml_timestamp,
                       job_language,
                       job_module,
                       CONCAT(u.user_prename,' ',u.user_name) AS job_uploader
                       FROM jobs
                       LEFT JOIN users u ON u.user_id = job_user_id
                       WHERE job_user_id = :user_id AND job_module = :job_module
                       AND (SELECT CONCAT(u.user_prename,' ',u.user_name) AS corrector_fullname
                                   FROM correctors
                                   JOIN users u ON u.user_id = corrector_corrector_id
                                   WHERE corrector_job_id = job_id)
                       AND (SELECT CONCAT(u.user_prename,' ',u.user_name) AS correction_validator_fullname
                                   FROM correction_validators
                                   JOIN users u ON u.user_id = correction_validator_correction_validator_id
                                   WHERE correction_validator_job_id = job_id)
                       ORDER BY job_id ASC";

Danke und Gruss
Dom

[dom77]

Hallo, der Post von 11:46 ist erledigt, habs folgendermaßen gelöst:

         SELECT job_id,
                       job_in_use,
                       job_initialized,
                       job_active,
                       job_user_id,
                       job_number,
                       job_name,
                       job_description,
                       job_path,
                       job_start,
                       job_edit,
                       job_status,
                       job_idml_filepath,
                       job_idml_timestamp,
                       job_language,
                       job_module,
                       CONCAT(u.user_prename,' ',u.user_name) AS job_uploader,
                       (SELECT CONCAT(u.user_prename,' ',u.user_name)
                                   FROM correctors
                                   JOIN users u ON u.user_id = corrector_corrector_id
                                   WHERE corrector_job_id = job_id) AS job_corrector_fullname,
                       (SELECT CONCAT(u.user_prename,' ',u.user_name)
                                   FROM correction_validators
                                   JOIN users u ON u.user_id = correction_validator_correction_validator_id
                                   WHERE correction_validator_job_id = job_id) AS job_correction_validator_fullname
                       FROM jobs
                       LEFT JOIN users u ON u.user_id = job_user_id
                       WHERE job_user_id = :user_id AND job_module = :job_module
                       ORDER BY job_id ASC

Erstmal danke für die Hilfe. Jetzt fehlt nur noch, die Abarbeitung von mehreren Einträgen in "Correctors" oder "Correction_Validators", denn wenn dies zutrifft kommt:

Cardinality violation: 1242 Subquery returns more than 1 row

Gruss
Dominik

[dom77]

Auch letzteres konnte ich lösen, die finale Lösung lautet also:

SELECT job_id,
                       job_in_use,
                       job_initialized,
                       job_active,
                       job_user_id,
                       job_number,
                       job_name,
                       job_description,
                       job_path,
                       job_start,
                       job_edit,
                       job_status,
                       job_idml_filepath,
                       job_idml_timestamp,
                       job_language,
                       job_module,
                       CONCAT(u.user_prename,' ',u.user_name) AS job_uploader,
                       (SELECT GROUP_CONCAT(CONCAT(u.user_prename,' ',u.user_name) SEPARATOR ', ')
                                   FROM correctors
                                   JOIN users u ON u.user_id = corrector_corrector_id
                                   WHERE corrector_job_id = job_id) AS job_corrector_fullname,
                       (SELECT GROUP_CONCAT(CONCAT(u.user_prename,' ',u.user_name) SEPARATOR ', ')
                                   FROM correction_validators
                                   JOIN users u ON u.user_id = correction_validator_correction_validator_id
                                   WHERE correction_validator_job_id = job_id) AS job_correction_validator_fullname
                       FROM jobs
                       LEFT JOIN users u ON u.user_id = job_user_id
                       WHERE job_user_id = :user_id AND job_module = :job_module
                       ORDER BY job_id ASC

Grüsse
Dom

heraus kommt:

[CODE]

Uploader: Max Mustermann (ist immer nur eine Person)

Korrekteure: Nina Wohlstand, Maria Erben, ...
Freigebende: Lars Pappel (z.B. wenn wir nur einen haben)

[CODE]

[defo]

sieht doch gut aus!
In Deinem ersten Versuch gehören die Subselects natürlich nicht in die Where-Bedingung des Hauptselects.
Ich hatte gemeint, das in den From Teil zu setzen als Join.

Ergebnis wäre eine Mehrfachnennung der Jobs gewesen (ohne Listung der Correctors bzw. Validators, sondern einzeln aufgeführt)