mySQL Joins

[schildi]

Hallo liebe Forumsgemeinde,

folgendes Problem mit einem Join über zwei Tabellen:

Tabelle nt_schulen in der Schuldaten hinterlegt sind, Tabelle members in der Daten von Lehren hinterlegt sind, Name etc. beide Tabellen haben ein Feld schul_nr.

Jetzt möchte ich alle Schulen finden denen noch kein Lehrer mit einer besonderen Funktion an der Schule ( Feld:besonderefunktion) zugeordnet sind.

Mein Query sieht so aus:
SELECT nt_schulen.schulnr FROM nt_schulen LEFT JOIN members ON nt_schulen.schulnr != members.schulnr WHERE members.besonderefunktion = 'Ja'

bei diesem Query erhalte ich aber ein sehr viel größeres Resultat alsdas es Schulen (6702) geben würde.

Ändere ich allerdings nt_schulen.schulnr = members.schulnr gibt es als Resultat die 181 Schulen bzw. Schulnummern zurück denen ein Lehrer mit besondere Funktion an der Schule zugeordnet ist.

Warum erhalte ich bei != nicht das richtige Ergebniss, es sollte mir nach meiner Logik doch alle Schulen liefern die noch keinen Lehrer zugeordnet haben, wenn = mir alle Schulen liefert an denen Lehrer zugeordnet sind.

Ich denke ich habe da nen dicken Knoten im Kopf, wäre schön wenn mir da jemand beim lösen helfen könnte.

Gruß Jan

[Rouven]

Hello,

Ich denke ich habe da nen dicken Knoten im Kopf, wäre schön wenn mir da jemand beim lösen helfen könnte.

ja, diesen Knoten sollten wir lösen. Zur Verdeutlichung sei vielleicht auch nochmal unsere entsprechende Sammlung an SELFHTML-Artikeln empfohlen.
Bitte führe dir vor Augen, was die ON-Klausel festlegt: sie bestimmt, wann zwei Datensätze innerhalb eines Joins als zusammen gehörend angesehen werden. Sie bewerten also NICHT das Ergebnis, sondern ob die Sätze zusammen passen. Dies kombiniert mit einem LEFT JOIN hat gewaltige Auswirkungen. Der Left Join garantiert, dass jeder Datensatz aus der linken Tabelle im Ergebnis auftaucht, lässt aber viel Spielraum was mit der "rechten Hälfte" ist. Diese kann leer sein (keine Übereinstimmung gem. ON) oder befüllt sein (Übereinstimmung bei ON). Je nach Wahl der Klausel hat dies nun folgende Auswirkung:
Tabelle 1     Tabelle 2
1 A           A #
2 B           D *
3 C           E -

Ein Left Join mit Gleichheit als Kriterium liefert
1 A A #
2 B
3 C
--> alle Sätze aus Tabelle 1, und nur der A Satz ergänzt um eine rechte Hälfte, bei B und C gibt es keine Übereinstimmung.

Ein Left Join mit Ungleichheit als Kriterium liefert ungleich was anderes:
1 A D *
1 A E -
2 B A #
2 B D *
2 B E -
3 C A #
3 C D *
3 C E -
--> beachte, dass du hier ALLES bekommen hast AUßER dem übereinstimmenden Satz A=A.

Du suchst schon prinzipiell die erste Variante mit Gleichheit, aber deine WHERE-Kriterien sind nicht angemessen. Du suchst nämlich solche Sätze, für die es entweder gar keinen Lehrer gibt ("rechte Hälfte" IS NULL) oder die zugeordneten Lehrer keine besondere Funktion haben:
SELECT nt_schulen.schulnr
FROM nt_schulen LEFT JOIN members
ON nt_schulen.schulnr == members.schulnr
WHERE
  (members.schulnr IS NULL)    -- hier gab es keine Übereinstimmun
AND (members.besonderefunktion IS NULL OR members.besonderefunktion <> 'Ja')   -- hier gab es einen Lehrer, aber er hat entweder keine Sonderfunktion oder es steht was anderes als Ja drin)

Als Verfeinerung könnte man nun dafür sorgen, dass jede Nummer nur einmal ausgegeben wird:
SELECT DISTINCT nt_schulen.schulnr
FROM nt_schulen LEFT JOIN members
ON nt_schulen.schulnr == members.schulnr
WHERE
  (members.schulnr IS NULL)    -- hier gab es keine Übereinstimmun
AND (members.besonderefunktion IS NULL OR members.besonderefunktion <> 'Ja')   -- hier gab es einen Lehrer, aber er hat entweder keine Sonderfunktion oder es steht was anderes als Ja drin)

...und bei genauerem Hinsehen stammen sowohl members.schulnr als auch members.besonderefunktion aus einer Tabelle, so dass wir dies im Hinblick auf den Join auch noch zusammenfassen können.
SELECT DISTINCT nt_schulen.schulnr
FROM nt_schulen LEFT JOIN members
ON nt_schulen.schulnr == members.schulnr
WHERE
  members.besonderefunktion IS NULL OR members.besonderefunktion <> 'Ja'

MfG
Rouven

[schildi]

Hallo,

vielen vielen Dank für deine ausfürliche Antwort, ich werde mir das ganze nun noch einmal durchlesen, aber ich denke der Knoten beginnt sich zu lösen.

Nochmals besten Dank, Jan

[Vinzenz Mai]

Hallo,

Jetzt möchte ich alle Schulen finden denen noch kein Lehrer mit einer besonderen Funktion an der Schule ( Feld:besonderefunktion) zugeordnet sind.

Mein Query sieht so aus:
SELECT nt_schulen.schulnr FROM nt_schulen LEFT JOIN members ON nt_schulen.schulnr != members.schulnr WHERE members.besonderefunktion = 'Ja'

bei diesem Query erhalte ich aber ein sehr viel größeres Resultat alsdas es Schulen (6702) geben würde.

das ist klar. Die meisten Lehrer, die ich kenne, lehren an genau einer einzigen Schule, ein paar an zwei verschiedenen. Sie sind *nicht* Lehrer an 25 anderen Schulen. Wenn nun ein solcher Lehrer eine Spezialfunktion hat und Du 27 Schulen hast, dann taucht er in 26 (bzw. 25) Datensätzen auf.

Warum erhalte ich bei != nicht das richtige Ergebniss, es sollte mir nach meiner Logik doch alle Schulen liefern die noch keinen Lehrer zugeordnet haben, wenn = mir alle Schulen liefert an denen Lehrer zugeordnet sind.

Oh nein. != ist *nicht* das was Du suchst. Du benötigst NULL-Werte und Deine WHERE-Bedingung ist ebenfalls falsch. Diese Bedingung gehört ebenfalls in die JOIN-Bedingung, siehe dazu diesen Archivbeitrag von mir.

[code lang=sql]
SELECT                              -- Gib mir
    s.schulnr                       -- alle ids der Schulen
FROM                                -- aus der Tabelle
    nt_schulen s                    -- nt_schulen, angesprochen über den
                                    -- Aliasnamen s (wie Schulen)
LEFT JOIN                           -- die mit der Tabelle
    members l                       -- members (angesprochen als l wie Lehrer)
ON                                  -- über die Bedingung gleicher
    s.schulnr = l.schulnr           -- Schulnummern
AND                                 -- und der Existenz
    l.besonderefunktion = 'Ja'      -- einer besonderen Funktion,
WHERE                               -- wobei wir nur an den Schulen
    l.schulnr IS NULL               -- interessiert sind, die keinen solchen
                                    -- aufweisen.

Alternativ ginge es auch mit einem Subselect.

Freundliche Grüße

Vinzenz

[schildi]

Hallo,

auch dir ein dickes Dankeschön für deine ausführliche Antwort, mit euren beiden Antworten beginnt sich der Knoten zu lösen.

Nochmals, besten Dank und lieben Gruß Jan