Abfrageproblem über mehrere Tabellen

Hallo,

in einer Tabelle Klassenarbeit sind Aufgaben und Lehrer festgehalten,
in der Tabelle Kl_Ergebnisse wird festgehalten, welcher Schüler welche
Aufgabe aus der Klassenarbeit richtig oder falsch gelöst hat. In der
Tabelle Schueler stehen die Schüler des entsprechenden Lehrers, in der
Tabelle Aufgaben alle überhaupt vorhandenen Aufgaben.

Mit
select *, Schueler.Nr as SSNr
from Kl_Ergebnisse, Schueler, Aufgaben, Klassenarbeit where
(Kl_Ergebnisse.Schuelernr=Schueler.Nr
and Schueler.Lehrer='$Usernr'
and Kl_Ergebnisse.Aufgabennr=Aufgaben.Nr
and Kl_Ergebnisse.geloest<>'j')
order by Schueler.SName, Schueler.SVorname, Kl_Ergebnisse.Aufgabennr

lasse ich alle Schüler des Lehrers mit den Aufgaben auswerfen, die
nicht richtig gelöst wurden. Wie bekomme ich in die Abfrage noch die
Aufgaben rein, die der Schüler überhaupt noch nicht gelöst hat?

Re: Abfrageproblem über mehrere Tabellen

St. Dreisbach wrote:
> Hallo,
>
> in einer Tabelle Klassenarbeit sind Aufgaben und Lehrer festgehalten,
> in der Tabelle Kl_Ergebnisse wird festgehalten, welcher Schüler welche
> Aufgabe aus der Klassenarbeit richtig oder falsch gelöst hat. In der
> Tabelle Schueler stehen die Schüler des entsprechenden Lehrers, in der
> Tabelle Aufgaben alle überhaupt vorhandenen Aufgaben.
>
> Mit
> select *, Schueler.Nr as SSNr
> from Kl_Ergebnisse, Schueler, Aufgaben, Klassenarbeit where
> (Kl_Ergebnisse.Schuelernr=Schueler.Nr
> and Schueler.Lehrer='$Usernr'
> and Kl_Ergebnisse.Aufgabennr=Aufgaben.Nr
> and Kl_Ergebnisse.geloest<>'j')
> order by Schueler.SName, Schueler.SVorname, Kl_Ergebnisse.Aufgabennr
>
> lasse ich alle Schüler des Lehrers mit den Aufgaben auswerfen, die
> nicht richtig gelöst wurden. Wie bekomme ich in die Abfrage noch die
> Aufgaben rein, die der Schüler überhaupt noch nicht gelöst hat?

Anders gesagt, du möchtest alle Aufgaben, außer denen, die ein Schüler
richtig gelöst hat.

Das geht dann mit LEFT JOIN. Wenn du den allerdings benutzt, solltest du
deine ,-Kreuzprodukte auch als INNER JOIN schreiben um keine
Überaschungen mit der Operatorreihenfolge zu erleben.


Grüße
Kai

Re: Abfrageproblem über mehrere Tabellen

Kai Ruhnau schrieb:

[..]
>>
>> Mit
>> select *, Schueler.Nr as SSNr
>> from Kl_Ergebnisse, Schueler, Aufgaben, Klassenarbeit where
>> (Kl_Ergebnisse.Schuelernr=Schueler.Nr
>> and Schueler.Lehrer='$Usernr'
>> and Kl_Ergebnisse.Aufgabennr=Aufgaben.Nr
>> and Kl_Ergebnisse.geloest<>'j')
>> order by Schueler.SName, Schueler.SVorname, Kl_Ergebnisse.Aufgabennr

[..]
>
>Anders gesagt, du möchtest alle Aufgaben, außer denen, die ein Schüler
>richtig gelöst hat.
>
>Das geht dann mit LEFT JOIN. Wenn du den allerdings benutzt, solltest du
>deine ,-Kreuzprodukte auch als INNER JOIN schreiben um keine
>Überaschungen mit der Operatorreihenfolge zu erleben.

Kann ich denn viermal ein left join setzen? Und welche Überraschung
kann mir die Operatorreihenfolge bringen? Du merkst, ich bin noch
nicht so fit in komplexeren Abfragen.
--
Gruß
Stephan

Re: Abfrageproblem über mehrere Tabellen

Stephan Dreisbach schrieb:

>Kai Ruhnau schrieb:
>
>[..]
>>>
>>> Mit
>>> select *, Schueler.Nr as SSNr
>>> from Kl_Ergebnisse, Schueler, Aufgaben, Klassenarbeit where
>>> (Kl_Ergebnisse.Schuelernr=Schueler.Nr
>>> and Schueler.Lehrer='$Usernr'
>>> and Kl_Ergebnisse.Aufgabennr=Aufgaben.Nr
>>> and Kl_Ergebnisse.geloest<>'j')
>>> order by Schueler.SName, Schueler.SVorname, Kl_Ergebnisse.Aufgabennr
>
>[..]

Ich habe mal folgendes probiert, liefert mir aber nicht das gewünschte
Ergebnis:
select *, Schueler.Nr as SSNr from Kl_Ergebnisse, Aufgaben
LEFT JOIN Schueler ON Schueler.Lehrer='$Usernr'
LEFT JOIN Klassenarbeit ON
Klassenarbeit.Aufgabe=Kl_Ergebnisse.Aufgabennr
where isnull(Klassenarbeit.Aufgabe)
or (Kl_Ergebnisse.Schuelernr=Schueler.Nr and
Kl_Ergebnisse.Aufgabennr=Aufgaben.Nr and Kl_Ergebnisse.geloest<>'j')
order by Schueler.SName, Schueler.SVorname, Kl_Ergebnisse.Aufgabennr";

Re: Abfrageproblem über mehrere Tabellen

Stephan Dreisbach wrote:
> Kai Ruhnau schrieb:
>
> [..]
>>> Mit
>>> select *, Schueler.Nr as SSNr
>>> from Kl_Ergebnisse, Schueler, Aufgaben, Klassenarbeit where
>>> (Kl_Ergebnisse.Schuelernr=Schueler.Nr
>>> and Schueler.Lehrer='$Usernr'
>>> and Kl_Ergebnisse.Aufgabennr=Aufgaben.Nr
>>> and Kl_Ergebnisse.geloest<>'j')
>>> order by Schueler.SName, Schueler.SVorname, Kl_Ergebnisse.Aufgabennr
>
> [..]
>> Anders gesagt, du möchtest alle Aufgaben, außer denen, die ein Schüler
>> richtig gelöst hat.
>>
>> Das geht dann mit LEFT JOIN. Wenn du den allerdings benutzt, solltest du
>> deine ,-Kreuzprodukte auch als INNER JOIN schreiben um keine
>> Überaschungen mit der Operatorreihenfolge zu erleben.
>
> Kann ich denn viermal ein left join setzen? Und welche Überraschung
> kann mir die Operatorreihenfolge bringen? Du merkst, ich bin noch
> nicht so fit in komplexeren Abfragen.

Mit

SELECT *
FROM Aufgaben
INNER JOIN Schueler -- komplettes Kreuzprodukt zwischen Aufgaben und
Schülern
LEFT JOIN Kl_Ergebnisse
ON Kl_Ergebnisse.Aufgabennr=Aufgaben.Nr
AND Kl_Ergebnisse.Schuelernr=Schueler.Nr
AND Kl_Ergebnisse.geloest='j'
-- Schau nach, ob die Aufgabe richtig gelöst wurde...
WHERE Schueler.Lehrer='$Usernr'
AND Kl_Ergebnisse.Aufgabennr IS NULL
-- ... und nimm nur diejenigen, bei denen kein "richtig
gelöst"-Datensatz existiert.

Das gibt dir alle Aufgaben-Schüler-Kombinationen, die nicht oder nicht
richtig gelöst wurden. Ich hab's mir jetzt erst genauer angeschaut:

- Wozu ist `Klassenarbeit` in deiner Abfrage?
- Soll jeder Schüler sämtliche Aufgaben lösen?
- Bzw. Wie ist festgelegt, welche Aufgaben überhaupt gelöst werden sollen?

Grüße
Kai

Re: Abfrageproblem über mehrere Tabellen

Kai Ruhnau schrieb:

>Stephan Dreisbach wrote:
>> Kai Ruhnau schrieb:
>>
>> [..]
>>>> Mit
>>>> select *, Schueler.Nr as SSNr
>>>> from Kl_Ergebnisse, Schueler, Aufgaben, Klassenarbeit where
>>>> (Kl_Ergebnisse.Schuelernr=Schueler.Nr
>>>> and Schueler.Lehrer='$Usernr'
>>>> and Kl_Ergebnisse.Aufgabennr=Aufgaben.Nr
>>>> and Kl_Ergebnisse.geloest<>'j')
>>>> order by Schueler.SName, Schueler.SVorname, Kl_Ergebnisse.Aufgabennr
>>
>> [..]
>>> Anders gesagt, du möchtest alle Aufgaben, außer denen, die ein Schüler
>>> richtig gelöst hat.
>>>
>>> Das geht dann mit LEFT JOIN. Wenn du den allerdings benutzt, solltest du
>>> deine ,-Kreuzprodukte auch als INNER JOIN schreiben um keine
>>> Überaschungen mit der Operatorreihenfolge zu erleben.
>>
>> Kann ich denn viermal ein left join setzen? Und welche Überraschung
>> kann mir die Operatorreihenfolge bringen? Du merkst, ich bin noch
>> nicht so fit in komplexeren Abfragen.
>
>Mit
>
>SELECT *
>FROM Aufgaben
>INNER JOIN Schueler -- komplettes Kreuzprodukt zwischen Aufgaben und
>Schülern
>LEFT JOIN Kl_Ergebnisse
> ON Kl_Ergebnisse.Aufgabennr=Aufgaben.Nr
> AND Kl_Ergebnisse.Schuelernr=Schueler.Nr
> AND Kl_Ergebnisse.geloest='j'
>-- Schau nach, ob die Aufgabe richtig gelöst wurde...
>WHERE Schueler.Lehrer='$Usernr'
> AND Kl_Ergebnisse.Aufgabennr IS NULL
>-- ... und nimm nur diejenigen, bei denen kein "richtig
>gelöst"-Datensatz existiert.
>
>Das gibt dir alle Aufgaben-Schüler-Kombinationen, die nicht oder nicht
>richtig gelöst wurden. Ich hab's mir jetzt erst genauer angeschaut:

Erstmal vielen Dank für die Hilfe.
>
>- Wozu ist `Klassenarbeit` in deiner Abfrage?
In der Tabelle Klassenarbeit mit den Spalten Lehrer und Aufgabe stehen
die ausgewählten Aufgaben des Lehrers: Aufgabe entspricht der
Kl_Ergebnisse.Aufgabennr sowie aus der Tabelle Aufgaben der Spalte Nr,
in der z.Zt. ca 2000 Aufgaben stehen.
>- Soll jeder Schüler sämtliche Aufgaben lösen?
Ja, alle die in der Tabelle Klassenarbeit stehen. Diese Einträge hat
der Lehrer bestimmt, wenn er aus dem gesamten Pool von Aufgaben einige
wenige zusammengestellt hat.
>- Bzw. Wie ist festgelegt, welche Aufgaben überhaupt gelöst werden sollen?
Wenn ein Schüler eine der Aufgaben aus der Tabelle Klassenarbeit
gelöst hat, kommt diese Aufgabe mit dem Hinweis auf richtig oder
falsch in die Tabelle Kl_Ergebnisse.

Meine Abfrage ganz oben funktioniert ja, sie prüft nur noch nicht, ob
alle Aufgaben aus der Tabelle Klassenarbeit auch in der Tabelle
Kl_Ergebnisse stehen. Der Lehrer soll nämlich nach einer gewissen Zeit
sehen, welche Aufgaben nicht oder falsch gelöst wurden, um ein
Arbeitsblatt zu generieren.

Re: Abfrageproblem über mehrere Tabellen

Ich selbst schrieb:

ich fange nochmal an, weil alle Versuche trotz Hilfe fehlschlugen:

In der Tabelle AUFGABEN stehen Matheaufgaben, NR ist die fortlaufende
eindeutige Kennzeichnung(id), AUFGABE ist die Aufgabenstellung.
In der Tabelle SCHUELER stehen tausende von Schülern mit Name und
Vorname, jeweils durch die Spalte LEHRER den verschiedensten Lehrern
zugeordnet. Die eindeutige ID ist NR.
Ein Lehrer stellt nun aus dem gesamten Pool von Aufgaben (AUFGABEN)
z.B. 10 zusammen, diese werden in der Tabelle KLASSENARBEIT notiert.
Diese Tabelle hat nur zwei Spalten, einmal LEHRER und einmal AUFGABE,
letztere identisch mit NR aus der Tabelle AUFGABEN.
Wenn ein Schüler eine Aufgabe gelöst hat, trage ich dessen id in die
Tabelle KL_ERGEBNISSE ein, genauer in die Spalte SCHUELERNR, die
Nummer der Aufgabe aus der Tabelle KLASSENARBEIT in die Spalte
AUFGABENNR, die zugehörige Lehrernummer in die Spalte LEHRERNR und ob
richtig oder falsch gelöst wurde in GELOEST.
Der Lehrer soll nämlich auf Klick ein Arbeitsblatt für jeden einzelnen
Schüler mit den falsch gelösten und gar nicht bearbeiteten Aufgaben
generieren können.

>Mit
>select *, Schueler.Nr as SSNr
>from Kl_Ergebnisse, Schueler, Aufgaben, Klassenarbeit where
> (Kl_Ergebnisse.Schuelernr=Schueler.Nr
> and Schueler.Lehrer='$Usernr'
> and Kl_Ergebnisse.Aufgabennr=Aufgaben.Nr
> and Kl_Ergebnisse.geloest<>'j')
>order by Schueler.SName, Schueler.SVorname, Kl_Ergebnisse.Aufgabennr
>
schaffe ich es aber nur die Aufgaben zu erfassen, die der Schüler
falsch gelöst hat.

Aber in der Abfrage sind leider nicht die Aufgaben erfasst, die der
Schüler aus der Tabelle KLASSENARBEIT gar nicht bearbeitet hat, denn
die sollen auch auf das Arbeitsblatt.
Ich bin immer noch ratlos (oder hilflos ;-)).

Stephan

Re: Abfrageproblem über mehrere Tabellen

Am 23.02.2007 16:06 schrieb Stephan Dreisbach:

> ich fange nochmal an, weil alle Versuche trotz Hilfe fehlschlugen:
>
> In der Tabelle AUFGABEN stehen Matheaufgaben, NR ist die fortlaufende
> eindeutige Kennzeichnung(id), AUFGABE ist die Aufgabenstellung.
> In der Tabelle SCHUELER stehen tausende von Schülern mit Name und
> Vorname, jeweils durch die Spalte LEHRER den verschiedensten Lehrern
> zugeordnet.

Mir jedenfalls hülfe die Ausgabe von SHOW CREATE TABLE mehr als diese
prosaischen Erklärungen. Ich verstehe z.B. nicht, wie Du mit *einer*
Spalte einen Schüler *mehreren* Lehrern zuordnest. Auf den ersten
Blick und ohne Beispieldaten liest sich das wie eine
nicht-normalisierte Struktur

> Die eindeutige ID ist NR.

Ist das jetzt dieselbe ID/NR wie oben in der Tabelle AUFGABEN, oder
eine andere?

> Ein Lehrer stellt nun aus dem gesamten Pool von Aufgaben (AUFGABEN)
> z.B. 10 zusammen, diese werden in der Tabelle KLASSENARBEIT notiert.

SHOW CREATE TABLE? Beispieldaten?

> Diese Tabelle hat nur zwei Spalten, einmal LEHRER und einmal AUFGABE,
> letztere identisch mit NR aus der Tabelle AUFGABEN.
Also könnte das etwa so aussehen:

LEHRER AUFGABE
1 11
1 12
1 13
1 14
1 15
1 16
1 17
1 18
1 19
1 20

wenn Lehrer "1" die Aufgaben 11 bis 20 stellen möchte. Richtig?

> Wenn ein Schüler eine Aufgabe gelöst hat, trage ich dessen id in die
> Tabelle KL_ERGEBNISSE ein, genauer in die Spalte SCHUELERNR, die
> Nummer der Aufgabe aus der Tabelle KLASSENARBEIT in die Spalte
> AUFGABENNR, die zugehörige Lehrernummer in die Spalte LEHRERNR und ob
> richtig oder falsch gelöst wurde in GELOEST.

Also etwa so:

Schuelernr Aufgabennr Lehrernr geloest
1 11 1 t
1 12 1 f
1 13 1 t
.....
1 20 1 f

> Der Lehrer soll nämlich auf Klick ein Arbeitsblatt für jeden einzelnen
> Schüler mit den falsch gelösten und gar nicht bearbeiteten Aufgaben
> generieren können.

Ah, es gibt also nicht nur richtig und flasch, sondern auch NULL in
'geloest'.

>
>> Mit
>> select *, Schueler.Nr as SSNr
>>from Kl_Ergebnisse, Schueler, Aufgaben, Klassenarbeit where
>> (Kl_Ergebnisse.Schuelernr=Schueler.Nr
>> and Schueler.Lehrer='$Usernr'
>> and Kl_Ergebnisse.Aufgabennr=Aufgaben.Nr
>> and Kl_Ergebnisse.geloest<>'j')
>> order by Schueler.SName, Schueler.SVorname, Kl_Ergebnisse.Aufgabennr
>>
> schaffe ich es aber nur die Aufgaben zu erfassen, die der Schüler
> falsch gelöst hat.
>
> Aber in der Abfrage sind leider nicht die Aufgaben erfasst, die der
> Schüler aus der Tabelle KLASSENARBEIT gar nicht bearbeitet hat, denn
> die sollen auch auf das Arbeitsblatt.
> Ich bin immer noch ratlos (oder hilflos ;-)).

Dann frag doch danach: AND (KL_Ergebnisse.geloest <> 'j' OR
KL_Ergebnisse.geloese IS NULL).

War das alles, oder habe ich jetzt was nicht verstanden?
Ist es vielleicht so, dass Du in Klassenarbeit gar nicht *alle*
Aufgaben-/Schülerkombinationen aufführst, sondern alle weglässt, die
der Schüler gar nichterst angefasst hat? Dann hülfe Dir möglicherweise
ein LEFT JOIN weiter -> Dokumentation, und auch hier schon zu genüge
durchgekaut.

Und kann es möglicherweise sein, dass die Kanone ein klein wenig groß
geraten ist mit der Du da auf einen ziemlich mickrigen Spatzen schießt?

Re: Abfrageproblem über mehrere Tabellen

Christian Kirsch schrieb:

>Mir jedenfalls hülfe die Ausgabe von SHOW CREATE TABLE mehr als diese
>prosaischen Erklärungen. Ich verstehe z.B. nicht, wie Du mit *einer*
>Spalte einen Schüler *mehreren* Lehrern zuordnest. Auf den ersten
>Blick und ohne Beispieldaten liest sich das wie eine
>nicht-normalisierte Strukturindeutige Kennzeichnung(id), AUFGABE ist die Aufgabenstellung.
>> In der Tabelle SCHUELER stehen tausende von Schülern mit Name und
>> Vorname, jeweils durch die Spalte LEHRER den verschiedensten Lehrern
>> zugeordnet.


Tab SCHUELER
NR NAME VORNAME LEHRER
23 Ka Fritz 12
28 Ba Hans 15
29 Da Karla 15
32 Da Karla 99
45 Da Karla 15
67 Da Karla 15

Tab AUFGABEN
NR Aufgabe ... ...
45 Text...
46 Text...
136 Text...
199 Text...
287 Text...
999 Text...
1005 Text...

Der Lehrer 15 stellt eine Arbeit zusammen:

Tab KLASSENARBEIT
LEHRER AUFGABE
15 136
15 287
15 999

Die Schüler des Lehrers 15 lösen einige Aufgaben:
Tab KL_ERGEBNISSE
LEHRERNR SCHUELERNR AUFGABENNR GELOEST
12 23 333 j
12 23 666 n
15 28 287 j
15 45 287 n
15 67 999 j
15 67 136 j
15 45 136 n
15 28 999 n

liefert bei meiner Abfrage (Lehrernr 15) nur
SCHUELERNR AUFGABENNR
28 999
45 136
45 287

soll aber liefern (bei Lehrernr 15):
SCHUELERNR AUFGABENNR
28 136
28 999
29 136
29 287
29 999
45 136
45 287
45 999
67 287

>Und kann es möglicherweise sein, dass die Kanone ein klein wenig groß
>geraten ist mit der Du da auf einen ziemlich mickrigen Spatzen schießt?

Wenn der Spatz so mickrig ist, hätte ich ihn längst erlegt.

Re: Abfrageproblem über mehrere Tabellen

Stephan Dreisbach schrieb:
> Christian Kirsch schrieb:
>
>> Mir jedenfalls hülfe die Ausgabe von SHOW CREATE TABLE mehr als diese
>> prosaischen Erklärungen. Ich verstehe z.B. nicht, wie Du mit *einer*
>> Spalte einen Schüler *mehreren* Lehrern zuordnest. Auf den ersten
>> Blick und ohne Beispieldaten liest sich das wie eine
>> nicht-normalisierte Strukturindeutige Kennzeichnung(id), AUFGABE ist die Aufgabenstellung.
>>> In der Tabelle SCHUELER stehen tausende von Schülern mit Name und
>>> Vorname, jeweils durch die Spalte LEHRER den verschiedensten Lehrern
>>> zugeordnet.
>
>
> Tab SCHUELER
> NR NAME VORNAME LEHRER
> 23 Ka Fritz 12
> 28 Ba Hans 15
> 29 Da Karla 15
> 32 Da Karla 99
> 45 Da Karla 15
> 67 Da Karla 15
>
> Tab AUFGABEN
> NR Aufgabe ... ...
> 45 Text...
> 46 Text...
> 136 Text...
> 199 Text...
> 287 Text...
> 999 Text...
> 1005 Text...
>
> Der Lehrer 15 stellt eine Arbeit zusammen:
>
> Tab KLASSENARBEIT
> LEHRER AUFGABE
> 15 136
> 15 287
> 15 999
>
> Die Schüler des Lehrers 15 lösen einige Aufgaben:
> Tab KL_ERGEBNISSE
> LEHRERNR SCHUELERNR AUFGABENNR GELOEST
> 12 23 333 j
> 12 23 666 n
> 15 28 287 j
> 15 45 287 n
> 15 67 999 j
> 15 67 136 j
> 15 45 136 n
> 15 28 999 n
>
> liefert bei meiner Abfrage (Lehrernr 15) nur
> SCHUELERNR AUFGABENNR
> 28 999
> 45 136
> 45 287
>
> soll aber liefern (bei Lehrernr 15):
> SCHUELERNR AUFGABENNR
> 28 136
> 28 999
> 29 136
> 29 287
> 29 999
> 45 136
> 45 287
> 45 999
> 67 287
>

Und wenn Du nun einfach NULL für die Aufgaben eintrügest, die der
Schüler nicht abgegeben hat?

>> Und kann es möglicherweise sein, dass die Kanone ein klein wenig groß
>> geraten ist mit der Du da auf einen ziemlich mickrigen Spatzen schießt?
>
> Wenn der Spatz so mickrig ist, hätte ich ihn längst erlegt.
>
Ich wollte mit meiner Bemerkung andeuten, dass eine Datenbank vielleicht
ein klein wenig übertrieben sein könnte für diese Aufgabenstellung
(welche das auch immer eigentlich sein mag). Meine Mathelehrer haben
unsere Klausuren jedenfalls einfach korrigiert - und wenn eine Aufgabe
nicht bearbeitet war, dann gab's halt dafür auch keine Punkte.

Aber vermutlich geht das so einfach heute nicht mehr.

Re: Abfrageproblem über mehrere Tabellen

Christian Kirsch schrieb:

[..]
>
>Und wenn Du nun einfach NULL für die Aufgaben eintrügest, die der
>Schüler nicht abgegeben hat?

Dann müsste er mindestens einmal versucht haben, die Aufgaben zu
lösen.

>
>>> Und kann es möglicherweise sein, dass die Kanone ein klein wenig groß
>>> geraten ist mit der Du da auf einen ziemlich mickrigen Spatzen schießt?
>>
>> Wenn der Spatz so mickrig ist, hätte ich ihn längst erlegt.
>>
>Ich wollte mit meiner Bemerkung andeuten, dass eine Datenbank vielleicht
>ein klein wenig übertrieben sein könnte für diese Aufgabenstellung
>(welche das auch immer eigentlich sein mag). Meine Mathelehrer haben
>unsere Klausuren jedenfalls einfach korrigiert - und wenn eine Aufgabe
>nicht bearbeitet war, dann gab's halt dafür auch keine Punkte.

Das angesprochene Problem ist nur ein winzig kleiner Teil des
Gesamtprojektes.

--
Stephan