SQL - Join und SubSelect

[Casablanca]

Hallo,

ich habe da folgendes Problem und sitze ich gerade auf dem Schlauch. Ich habe 5 Tabellen wie folgt:

User

UserId, Username, ...

Tasks

TaskId, TaskName, LanguageId, ...

Languages

LanguageId, Language, ...

und zwei Verknüfungstabellen:

UserLanguages

UserId, LanguageId, ...

UserTasks

UserId, TaskId, Status

Am Anfang bekommen die Benutzer alle Tasks für einer bestimmten Sprache, für die angemeldet sind, zu sehen. Die Benutzer können diese Tasks annehmen oder ablehnen. Erst dann wird die Tabelle UserTasks mit Daten befüllt. Der Status wird auf 0=abgelehnt oder auf 1=angenommen gesetzt. Die Benutzer, die einen Einsatz(Task) abgelehnt haben, dürfen bei dem nächsten Aufruf der Seite die abgelehnten Einsätze nicht mehr sehen. Das Problem ist, ich muss die UserTasks-Tabelle in meiner SQL-Abfrage aufnehmen, die zu Anfang für den jeweiligen Benutzer keinen Eintrag hat, bis er einen Auftrag annimmt bzw. ablehnt. Da bekomme ich dann aber keine korrekte Ergebnisse. Ich habe das hier versucht.

SELECT UserLanguages.UserId, Languages.Language, Tasks.TaskId
FROM UserLanguages 
  Left Join Tasks on UserLanguages.LanguageId=Tasks.LanguageId 
  Left join  Languages on Tasks.LanguageId=Languages.LanguageId 
  left join [hier muss die Tabelle UserTasks hinein] 
where UserLanguages.UserId=VARIABLE  

Ich vermute, dass in den [] eine SubSelect-Query stehen muss, die eine Menge ausgibt, die auch leer sein darf und ich trotzdem die korrekten Records aus den anderen Tabellen zurück bekommen kann.

Danke im Voraus.

Edit Rolf B: SQL mit Zeilenumbrüchen versorgt

[MudGuard]

Hi,

User

UserId, Username, ...

Tasks

TaskId, TaskName, LanguageId, ...

Languages

LanguageId, Language, ...

und zwei Verknüfungstabellen:

UserLanguages

UserId, LanguageId, ...

UserTasks

UserId, TaskId, Status

SELECT UserLanguages.UserId, Languages.Language, Tasks.TaskId
FROM UserLanguages 
  Left Join Tasks on UserLanguages.LanguageId=Tasks.LanguageId 
  Left join  Languages on Tasks.LanguageId=Languages.LanguageId 
  left join [hier muss die Tabelle UserTasks hinein] 
where UserLanguages.UserId=VARIABLE  

Hm. Ausgehend von den UserLanguages gibt es mehrere Wege zu den Usertasks.

UserLanguages.userId -> UserTasks.userId

UserLanguages.taskId -> Tasks.taskId -> Usertasks.taskId

Welcher Weg davon der Richtige ist, kannst nur Du beurteilen.

Einen Subselect sehe ich hier nicht. LEFT JOIN scheint mir ausreichend.

Aber wie gesagt, wirklich beurteilen kannst das nur Du - Deine Beschreibung ist mir zu wirr ...

cu,
Andreas a/k/a MudGuard

[Rolf B]

Hallo Casablanca,

zum einen - bist Du sicher, dass Du für die Kombination von UserLanguages, Tasks und Languages einen LEFT JOIN brauchst? Sicherlich wird es keinen Task geben, der eine unbekannte LanguageId hat. Und es wird auch keinen UserLanguage Satz geben, der eine unbekannte LanguageId hat.

Insofern wirst Du wohl keine Ergebniszeilen bekommen, wo aus der Tasks- oder Language-Abfrage nichts herauskommt. Und es wäre fachlich auch nicht sinnvoll, glaube ich. Man sollte keinen LEFT JOIN formulieren, wo fachlich ein INNER JOIN angemessen ist.

Soweit dazu. Zu deinem Problem: Du könntest einen LEFT JOIN auf die UserTasks machen (hier ist ein LEFT JOIN korrekt, weil es ja auch keine Treffer geben kann) und dann STATUS IS NULL OR STATUS=1 prüfen. STATUS <> 0 könnte auch gehen, NULL ist ungleich zu allem. Musst Du ausprobieren.

SELECT ul.UserId, l.Language, t.TaskId
FROM UserLanguages ul
  Join Tasks t ON ...
  Join Languages l ON ...
  Left Join UserTasks ON ut.UserId=ul.UserId AND ut.TaskId=t.TaskId 
WHERE ul.UserId = VARIABLE  
-- verwende dies:
  AND (ut.status IS NULL OR ut.status = 1)
-- oder dies:
  AND (ut.status <> 0)

Ich habe die Filterbedingung

Es ist bei umfangereicheren Queries oft lesbarer, den Tabellen Aliasnamen zu geben, dann schreibt man nicht so viel und das Statement ist übersichtlicher. Das habe ich einfach mal gemacht.

Aber ich glaube, es ist einfacher (und vermutlich auch effizienter), wenn Du nicht nach den Sätzen fragst, die Du haben willst, sondern nach denen, die Du nicht haben willst: mit NOT EXISTS (...)

SELECT ul.UserId, l.Language, t.TaskId
FROM UserLanguages ul
  Join Tasks t ON ...
  Join Languages l ON ...
WHERE ul.UserId = VARIABLE  
  AND NOT EXISTS (SELECT * FROM UserTasks ut
                  WHERE ut.UserId=ul.UserId AND ut.TaskId=t.TaskId 
                    AND ut.Status=0)

Probier beides aus, mach Explains, miss die Performance. Verwende das, was besser läuft oder was Du besser verstehst 😉

Rolf

[Casablanca]

Hallo Rolf,

vielen Dank für deine Antwort und deine Bemühung. Ich bekomme leider bei beiden Anweisungen Fehlermeldungen:

#1064 - Fehler in der SQL-Syntax. Bitte die korrekte Syntax im 
Handbuch nachschlagen bei 'Join Languages l ON WHERE ul.UserId = 1 
AND NOT EXISTS (SELECT * FROM UserTasks ' in Zeile 1

Du hast aber vollkommen Recht. Man braucht nur für die UserTasks-Tabelle eine Left Join-Anweisung. Man bekommt dann für alle Records mit Status 1 einen Wert für UserTaskId. Das Problem ist, das ich dann keinen Unterschied zwischen dem 0- und Null-Wert, also nicht vorhanden, machen kann. Bei einem Status=1 weiß ich, dass jemand den Einsatz angenommen hat. Bei dem fehlenden Wert im UserTaskId weiß ich aber nicht, ob der Einsatz abgelehnt oder überhaupt keinen Eintrag vorhanden ist. Ich muss also nur dann die Einsätze anzeigen, die entweder angenommen sind oder, weder angenommen noch abgelehnt sind. Das heißt, es gibt für den entsprechenden Benutzer noch keinen Antrag in der UserTasks-Tabelle vorhanden (über UserId und TaskId der Tablle UserTasks).

Danke

[Rolf B]

Hallo Casablanca,

ich kann's ja kaum glauben, aber wenn die Meldung 1:1 dein SQL zeigt, dann hast Du meine "..." einfach weggemacht, statt deine bisherigen ON Bedingungen wieder einzusetzen.

Ich hab die "..." doch nur gemacht, um nicht zu viel schreiben zu müssen.

Ich muss also nur dann die Einsätze anzeigen, die entweder angenommen sind oder, weder angenommen noch abgelehnt sind.

Ja. Schrieb ich doch. Der LEFT JOIN liefert dann NULL oder 1. Also:

(STATUS IS NULL OR STATUS = 1)

Bitte eingeklammert lassen, wenn Du das mit irgendwas AND-verknüpfst. AND hat Vorrang vor OR und würde ohne Klammern die Logik frittieren.

Aber - wie auch geschrieben - der NOT EXISTS könnte effizienter sein. Wenn Du ihn richtig einbaust 😉

Rolf

[Casablanca]

Hallo Rolf,

danke. Ich glaube, dass es so funktionieren sollte:

SELECT ul.UserId, l.Language, t.TaskId FROM UserLanguages ul 
Left Join Tasks t on ul.LanguageId=t.LanguageId Left join 
Languages l on t.LanguageId=l.LanguageId where ul.UserId=1 
AND NOT EXISTS (SELECT * FROM UserTasks ut WHERE 
ut.UserId=ul.UserId AND ut.TaskId=t.TaskId AND ut.Status=0) 

Ich muss nur noch im Einsatz herausfinden, ob dies auch mit Join statt left join funktionieren würde.

Gruß