verknüpfte SQL Anfrage über 3 Tabellen

[Felix80]

Hallo Leute,

ich grübele über einem SQL Query:

Tabelle1 enthält Wörter und jeweils eine ID, z.B. nWord (varchar) und nID (SmallInt). Tabelle3 enthält Benutzer und ihre ID: uName (varchar) und uID (SmallInt). Tabelle3 enthält eine n-zu-n Relation, welcher Nutzer bereits welches Wort entweder übersetzen konnte oder nicht (es handelt sich um einen Vokabeltrainer): sWordID (Varchar), sUserId (Varchar) und sStatus (TinyInt). sStatus=1 heißt, er/sie hat das Wort gewusst, sStatus=0, daß nicht. Nun möchte ich für einen bestimmten Benutzer ein zufälliges Wort ermitteln, welches er entweder nicht gewusst hat (sStatus=0) oder welches er noch nicht probiert hat (kein Eintrag in Tabelle3). Diese beiden Bedingungen bekomme ich nicht unter einen Hut und bitte die SQL-Cracks um Hilfe.

Es grüßt
Felix

[Frank (no reg)]

Hallo,

erstmal, welches Datenbanksystem hammerdenn?

Ansonsten helfen dir sicher korrelierende Unterabfragen ...

Und bekommst du es denn getrennt voneinander hin? Und wenn ja, wie und wie sieht dein Code dazu aus? Was hast du denn schon versucht?

Ciao, Frank

[Felix80]

Laufen soll es unter MySql 4.0.

Tabelle1 enthält Wörter und jeweils eine ID, z.B. nWord (varchar) und nID (SmallInt). Tabelle3 enthält Benutzer und ihre ID: uName (varchar) und uID (SmallInt). Tabelle3 enthält eine n-zu-n Relation, welcher Nutzer bereits welches Wort entweder übersetzen konnte oder nicht (es handelt sich um einen Vokabeltrainer): sWordID (Varchar), sUserId (Varchar) und sStatus (TinyInt). sStatus=1 heißt, er/sie hat das Wort gewusst, sStatus=0, daß nicht. Nun möchte ich für einen bestimmten Benutzer ein zufälliges Wort ermitteln, welches er entweder nicht gewusst hat (sStatus=0) oder welches er noch nicht probiert hat (kein Eintrag in Tabelle3). Diese beiden Bedingungen bekomme ich nicht unter einen Hut und bitte die SQL-Cracks um Hilfe.

Abfrage auf ein Wort, das Benutzer xy bereits falsch beantwortet hat:
select nWord from Tabelle1 left join Tabelle3 on Tabelle1.nID=Tabelle3. sWordID where sUserId=xy and sStatus=0 order by rand() limit 1

Abfrage auf ein Wort, das Benutzer xy noch nicht aufgerufen hat:
select nWord from Tabelle1 left join Tabelle3 on Tabelle1.nID=Tabelle3. sWordID where [...] order by rand() limit 1

Weiter kam ich nicht.

Gruß, Felix

[Frank (no reg)]

Okay, aufgrund von MySQL 4.0 scheiden dann wohl Subqueries leider aus, was relativ schade ist. Da müsste ich erstmal ne Weile sinnieren.

Besteht eventuell die Möglichkeit, dass du die Abfragen einzeln ausführst und dann eines der beiden Ergebnisse einfach nimmst. Im Maximalfall solltest du ja jeweils einen Datensatz bekommen und mit ODER als Verknüpfung nimmst du einfach einen von den beiden. Hmm ... Dann könntest du natürlich auch ein UNION zwischen die beiden Abfragen setzen, damit du nur eine Ergebnismenge zurückbekommst.

Und dir fehlt die WHERE Klausel für den zweiten Fall. Imho wäre diese einfach WHERE Tabelle3.sWordID IS NULL, denn du möchtest ja die Datensätze, die auf der linken Seite (left) ein nWord haben, auf der rechten Seite (also in Tabelle3) keine Entsprechung ... also NULL ...

Auf NULL prüft man mit IS NULL oder IS NOT NULL

HTH, Ciao, Frank

[Rouven]

Hello,

Abfrage auf ein Wort, das Benutzer xy bereits falsch beantwortet hat:
select nWord from Tabelle1 left join Tabelle3 on Tabelle1.nID=Tabelle3. sWordID where sUserId=xy and sStatus=0 order by rand() limit 1

OK

Abfrage auf ein Wort, das Benutzer xy noch nicht aufgerufen hat:
select nWord from Tabelle1 left join Tabelle3 on Tabelle1.nID=Tabelle3. sWordID where [...] order by rand() limit 1

hast du den [...]-Teil? Wenn nicht, der müsste sinngemäß auf den leeren JOIN prüfen: WHERE Tabelle3.sWordID IS NULL
falls es mit Tabelle2.sWordID nicht klappt, nimm eine andere Spalte und füge sie ggf. vorne dem SELECT hinzu. Wir wollen prüfen, dass kein Wert zurückkam weil der Join nicht ausgeführt wurde (siehe Left-Join).

Weiter kam ich nicht.

Ich weiß nicht, wie MySQL darauf jetzt reagiert, aber wenn du beide SELECTs per UNION zusammenwirfst und bei der oberen noch der ORDER BY entfernst (das muss bei UNION IIRC beim letzten Statement stehen und gilt dann für das Gesamtergebnis) könntest du das gewünschte Ergebnis haben.
Beachte, dass bei beiden Abfragen die Anzahl/Typen der Spalten übereinstimmen muss. Solltest du also bei der zweiten Abfrage noch eine Spalte ins Select aufgenommen haben, solltest du bei der ersten Abfrage eine  entsprechende Blindspalte aufnehmen, z.B.
SELECT nWord, '' AS dummy FROM Tabelle1...
UNION
SELECT nWord, Tabelle3.anderespalte FROM Tabelle1...
ORDER BY rand() limit 1

MfG
Rouven

[Ilja]

yo,

die abfrage sollte recht trivial sein, ein OUTER JOIN in Verbindung mit einem UNION ALL ist der richige ansatz, wobei ich davon ausgehe, dass die tabelle2 die user enthält. bedenke, dass die spaltenanzahl beim UNION operator immer gleich sein muss.

SELECT t1.nWord
FROM tabelle2 t2
INNER JOIN tabelle3 t3 ON t2.uID = t3.uID
INNER JOIN tabelle2 t1 ON t1.nID = t3.nID
WHERE t2.uID = id_Wert_des_Users
AND t3.sStatus = 0
UNION ALL
SELECT t4.nWord
FROM tabelle1 t4
LEFT JOIN tabelle3 t5 ON t5.nID = t4.nID
LEFT JOIN tabelle2 t6 ON (t6.uID = t5.uID AND t6.uID = id_Wert_des_Users)
WHERE t6.uID IS NULL
;

diese sollte dir deine menge geben, daraus musst du nur noch zufällig welche auswählen, wobei ich die random funktionsweise von mysql nicht im kopf habe, die doku sollte dir da weiter helfen.

Gruß
Ilja