SQL Abfrage

[Josette]

Hi Leute! Ich bin totale SQL Anfängerin und habe eine SQL Abfrage, wo ich einfach nicht verstehe, warum sie nicht stimmt. Also ich habe folgende 3 Tabellen:
KURS (KursNr, Bezeichnung, Leiter, Preis)
TEILNAHME (KursNr, TeilnehmerNr) und
TEILNEHMER (TeilnehmerNr, Name)

Meine Abfrage soll folgendes ausgeben: Liste aller Teilnehmer, die an mind. einem, aber an keinem Kurs von Fr. Huber teilnehmen.

Mein Lösungsansatz:

SELECT Name
FROM Teilnehmer
WHERE TeilnehmerNr IN
(SELECT TeilnehmerNr
FROM Teilnahme
WHERE KursNr NOT IN
(SELECT KursNr
FROM Kurs
WHERE Leiter = 'Huber'))

Bin über jede Hilfe sehr dankbar!
Liebe Grüße!

 

[Kampfgummibaerlie]

bei mir wirft die Abfrage keinen Fehler, nach schnellem Nachbauen der Datenbank.

Kannst du den Fehler posten?

EDIT: Versuche nie nach individuellem Text zu suchen, weil das langfristig viele Fehler verursachen kann. (hier die Where Leiter = 'Huber' angesehen)

Und wenn, dann sollte man die lower funktion nutzen, sprich where lower(leiter) = lower('Huber')

Des weiteren wäre relevant, wie umfangreich die Datenbank eingesetzt werden soll, sprich auf einer Homepage, oder wo anders?

 

[akretschmer]

Du wirst aggregieren müssen, welcher Teilnehmer welche Kurse besucht hat, und dann prüfen, ob in dieser aggregierten Menge die Frau Huber vorkommt. Lösung für PostgreSQL:

postgres=# create table teilnehmer(nr int primary key, name text);
CREATE TABLE
postgres=# create table kurs(nr int primary key, name text, leiter text);
CREATE TABLE
postgres=# create table teilname(kurs int references kurs, tn int references teilnehmer);
CREATE TABLE
postgres=# insert into kurs values (1,'kurs1','müller');
INSERT 0 1
postgres=# insert into kurs values (2,'kurs2','huber');
INSERT 0 1
postgres=# insert into kurs values (3,'kurs3','max');
INSERT 0 1
postgres=# insert into teilnehmer select s, 'teilnehmer'||s::text from generate_series(1,5) s;
INSERT 0 5
postgres=# insert into teilname values (1,1);
INSERT 0 1
postgres=# insert into teilname values (1,2);
INSERT 0 1
postgres=# insert into teilname values (1,4);
INSERT 0 1
postgres=# insert into teilname values (2,4);
INSERT 0 1
postgres=# insert into teilname values (2,5);
INSERT 0 1
postgres=# insert into teilname values (3,2);
INSERT 0 1
postgres=# insert into teilname values (3,3);
INSERT 0 1
postgres=# with tmp as (select t.name, array_agg(k.nr) from teilname tn left join teilnehmer t on tn.tn=t.nr left join kurs k on tn.kurs=k.nr group by t.name) select * from tmp where not array_agg  @> (select array_agg(nr) from kurs where leiter = 'huber');
    name     | array_agg 
-------------+-----------
 teilnehmer2 | {1,3}
 teilnehmer1 | {1}
 teilnehmer3 | {3}
(3 rows)

Allerdings wird das in MySQL nicht 1:1 so laufen, daß mußt Du anpassen.

 

[Josette]

Hi, ja klar! Ich hänge auch noch die Tabellen an, vl habe ich einen Fehler oben gemacht in der Beschreibung der Tabellen. Also ich bekomme mit meiner Abfrage die Namen Lauch, Wallner, Steyrer, Mueller, Hofer obwohl es eigentlich nur Mueller, Steyrer und Hofer sein sollten. Danke dir schon mal!

 

[ukulele]

Mal abgesehen von den Feinheiten mit lower() oder anderen Methoden das eleganter / performanter umzusetzen stimmt entweder deine Abfrage nicht oder dein Aufgabentext. Meine Lösung wäre

SELECT    *
FROM    TEILNEHMER
WHERE    TeilnehmerNr IN ( SELECT TeilnehmerNr FROM TEILNAHME )
AND        TeilnehmerNr NOT IN ( SELECT TEILNAME.TeilnehmerNr FROM TEILNAME INNER JOIN KURS ON TEILNAME.KursNr = KURS.KursNr WHERE KURS.Leiter = 'Huber' )

Weil kein Teilnehmer gelistet werden soll der irgendeinen Kurs bei Huber hatte. Also auch nicht wenn er einem Kurs von jemand anderem beigewohnt hat der nicht Huber ist.

 

[Josette]

Ein großes Danke für eure schnelle Hilfe! Also ich lerne nur für eine Klausur und es geht mir rein um das Verständnis. Ich würde nur gerne verstehen, warum meine Anfrage diese zwei extra Namen ausspuckt, obwohl ich mit .... NOT IN SELECT KursNr FROM Kurs WHERE Leiter = 'Huber' die KursNr von Fr. Huber eigentlich ausschließe. Hier nochmal mein gesamter Lösungsansatz:

SELECT Name
FROM Teilnehmer
WHERE TeilnehmerNr IN
(SELECT TeilnehmerNr
FROM Teilnahme
WHERE KursNr NOT IN
(SELECT KursNr
FROM Kurs
WHERE Leiter = 'Huber'))

 

[ukulele]

Mal angenommen du hast einen Teilnehmer A der hat Kurs X und Kurs Y gebucht. Kurs Y wird von Huber geleitet.

In deiner Abfrage liefert jetzt die 2te Unterabfrage (WHERE Leiter = 'Huber') Y zurück. Die erste Unterabfrage (WHERE KursNr NOT IN) gibt korrekter Weise die Beziehung Teilnehmer A zu Kurs X zurück, nicht Teilnehmer A zu Kurs Y. Teilnehmer A wird dir also im eigentlichen Select als Ergebnis geliefert denn er ist ja Teilnehmer von Kurs X.

Deine Aufgabenstellung lese ich so das Teilnehmer A nicht im Ergebnis sein darf denn "aber an keinem Kurs von Huber teilnehmen" bedeutet ja, es darf kein Kurs mit Huber existieren. Das kann man auf verschiedenen Wegen umsetzen, meiner sollte das möglichst simpel tun:

SELECT    *
FROM    TEILNEHMER
WHERE    TeilnehmerNr IN ( SELECT TeilnehmerNr FROM TEILNAHME )
AND        TeilnehmerNr NOT IN ( SELECT TEILNAME.TeilnehmerNr FROM TEILNAME INNER JOIN KURS ON TEILNAME.KursNr = KURS.KursNr WHERE KURS.Leiter = 'Huber' )

Alle Teilnehmer die a) irgendwo in einer Beziehung zu einem Kurs stehen (erste Unterabfrage) aber nicht b) in einem Kurs mit Huber sind (zweite Unterabfrage).

 

[dabadepdu]

SELECT Name
FROM Teilnehmer
WHERE TeilnehmerNr IN
(SELECT TeilnehmerNr
FROM Teilnahme
WHERE KursNr NOT IN
(SELECT KursNr
FROM Kurs
WHERE Leiter = 'Huber'))

Du schließt zwar zuerst alle Kurse von Frau Huber aus, aber als "Ergebnis" davon nimmst Du nur die TeilnehmerNR mit. Das kann eine nahezu x-beliebige sein. Denn sobald Teilnehmer bei Huber UND in irgendeinem anderen Kurs dabei sind, wird dort immer noch ihre Teilnehmer Nummer ausgegeben. Sie resultiert dann aus einem anderen Kurs. (Dein

Dies müsstest Du auch sehen, wenn Du nur die unteren 2 Selects ausführst (in der ersten Klammerebene). Hier sollten alle TN so oft auftauchen, wie sie in Tabelle Teilnahme aufgeführt sind (u.U. mehrfach- was bereits ein Manko ist*), minus die TeilnahmeNr in Huberkursen. Da müsste Dein Störenfried schon auffallen.
Deutlicher wird es vielleicht, wenn Du dieses Select Statement :
(SELECT TeilnehmerNr
FROM Teilnahme
WHERE KursNr NOT IN
(SELECT KursNr
FROM Kurs
WHERE Leiter = 'Huber'))
um die Ausgabe der KursNr erweiterst. Dann siehst Du, dass Dein "falscher" Teilnehmer, aus einem anderen Kurs kommt.

Dass Du mit dem obersten Select dann nur noch den Namen dazu ausgibst, macht keinen Unterschied mehr in der Datenmenge.
Insgesamt solltest Du nicht jedes Problem mit WHERE (NOT) IN angehen. Du hast hier 2 Alternative Lösungen. Die gängige Verbindung zwischen Tabellen ist ein JOIN. Klassisch dient WHERE (NOT) IN zur Inclusion oder Exclusion einzelner Werte. Ich geh mal davon aus, dass Euch JOIN auch gezeigt wurde und Du kannst Dir an den Unterlagen sicher klar machen, dass die Ausdruckskraft und Einsatzzwecke dort anders bzw. besser sind, als mit.

* Die Mehrfachselektion ist hier nicht kritisch, erzeugt aber bei großen Datenmengen unnötige Last.

 

[Josette]

Vielen lieben Dank für eure schnelle und sehr verständliche Hilfe! Das hat mir wirklich total geholfen!