SQL-Abfrage für m:n-Verknüpfung

Hallo zusammen,

irgendwie stehe ich gerade bei einer SQL-Abfrage auf dem Schlauch....
Folgendes ist gegeben:

Tabelle fahrzeuge
====
id (PK)
hersteller
typ

Tabelle kategorien
====
id (PK)
name

Tabelle fahrzeugeKategorien
===
fid (PK)
kid (PK)

Jedes Fahrzeug kann also mehrere Kategorien haben und zudem kann eine
Kategorie auch mehreren Fahrzeugen zugeordnet werden (m:n).
Die Abfrage zu welchen Kategorien ein Fahrzeug gehört ist trivial:
SELECT k.id, k.name
FROM kategorien k
LEFT JOIN fahrzeugeKategorien fk ON k.id=fk.kid
WHERE fk.fid=? ORDER BY k.name

Jetzt aber meine Frage: wie lautet die SQL-Abfrage für die Kategorien, zu
denen das Fahrzeug NICHT gehört?
Meine erste (für meinen Geschmack naheliegenste Lösung) wäre gewesen:
SELECT DISTINCT k.id, k.name
FROM kategorien k
LEFT JOIN fahrzeugeKategorien fk ON k.id=fk.kid
WHERE ISNULL(fk.fid) OR fk.fid!=? ORDER BY k.name
Aber die funktioniert natürlich nicht.

Jetzt bin ich nach langem rumprobieren auf die folgende funktionsfähige
Lösung gekommen:
SELECT k.id, k.name, SUM(IF(f.id=?,1,0)) AS a
FROM kategorien k
LEFT JOIN fahrzeugeKategorien fk ON k.id=fk.kid
LEFT JOIN fahrzeuge f ON f.id=fk.fid
GROUP BY k.id
HAVING a=0
ORDER BY k.name
Das scheint mir dann doch aber etwas umständlich zu sein (2 Joins,
IF-Konstrukt und Aggregation). Gibt es für einen solchen Fall nicht
irgendwie etwas elganteres (und vermutlich auch schnelleres)?

Herzlichen Dank schonmal...
Schönen Gruß

Stefan

Stefan Gehrig

Re: SQL-Abfrage für m:n-Verknüpfung

On Sat, 13 Aug 2005 11:12:04 +0200, Stefan Gehrig wrote:

>Jedes Fahrzeug kann also mehrere Kategorien haben und zudem kann eine
>Kategorie auch mehreren Fahrzeugen zugeordnet werden (m:n).

http://www.heddesheimer.de/coaching/warengruppen.html

sind zwar Artikel und Warengruppen, aber sonst wohl die gleiche
Aufgabe.

Gruß Marian

--
Kostenlose Tipps und Tricks zu PHP und MySQL
http://www.heddesheimer.de/coaching/

Re: SQL-Abfrage für m:n-Verknüpfung

Stefan Gehrig wrote:
> Hallo zusammen,
>
> irgendwie stehe ich gerade bei einer SQL-Abfrage auf dem Schlauch....
> Folgendes ist gegeben:
>
> Tabelle fahrzeuge
> ====
> id (PK)
> hersteller
> typ
>
> Tabelle kategorien
> ====
> id (PK)
> name
>
> Tabelle fahrzeugeKategorien
> ===
> fid (PK)
> kid (PK)
>
> Jedes Fahrzeug kann also mehrere Kategorien haben und zudem kann eine
> Kategorie auch mehreren Fahrzeugen zugeordnet werden (m:n).
> Die Abfrage zu welchen Kategorien ein Fahrzeug gehört ist trivial:
> SELECT k.id, k.name
> FROM kategorien k
> LEFT JOIN fahrzeugeKategorien fk ON k.id=fk.kid
> WHERE fk.fid=? ORDER BY k.name
>
> Jetzt aber meine Frage: wie lautet die SQL-Abfrage für die Kategorien, zu
> denen das Fahrzeug NICHT gehört?
> Meine erste (für meinen Geschmack naheliegenste Lösung) wäre gewesen:
> SELECT DISTINCT k.id, k.name
> FROM kategorien k
> LEFT JOIN fahrzeugeKategorien fk ON k.id=fk.kid
> WHERE ISNULL(fk.fid) OR fk.fid!=? ORDER BY k.name
> Aber die funktioniert natürlich nicht.
>

Versuche dies:

SELECT DISTINCT k.id, k.name
FROM kategorien k
LEFT JOIN fahrzeugeKategorien fk ON k.id=fk.kid AND fk.fid=?
WHERE ISNULL(fk.kid) ORDER BY k.name

Bis denn dann
Stefan

>...
>
Stefan Gehrig
>
>

Re: SQL-Abfrage für m:n-Verknüpfung

Stefan Gehrig wrote:

> Tabelle fahrzeuge
> ====
> id (PK)
> hersteller
> typ
>
> Tabelle kategorien
> ====
> id (PK)
> name
>
> Tabelle fahrzeugeKategorien
> ===
> fid (PK)
> kid (PK)
>

[snip]

> Jetzt aber meine Frage: wie lautet die SQL-Abfrage für die Kategorien, zu
> denen das Fahrzeug NICHT gehört?

[snip]

> Jetzt bin ich nach langem rumprobieren auf die folgende funktionsfähige
> Lösung gekommen:
> SELECT k.id, k.name, SUM(IF(f.id=?,1,0)) AS a
> FROM kategorien k
> LEFT JOIN fahrzeugeKategorien fk ON k.id=fk.kid
> LEFT JOIN fahrzeuge f ON f.id=fk.fid
> GROUP BY k.id
> HAVING a=0
> ORDER BY k.name
> Das scheint mir dann doch aber etwas umständlich zu sein (2 Joins,
> IF-Konstrukt und Aggregation). Gibt es für einen solchen Fall nicht
> irgendwie etwas elganteres (und vermutlich auch schnelleres)?

Wenn dein DBMS Subselects unterstützt gibt es noch folgende Alternative
zu Stefans JOIN-Ansatz:

SELECT k.id, k.name
FROM kategorien k
WHERE k.id NOT IN (
SELECT fk.kid
FROM fahrzeugekategorien fk
WHERE fk.fid=?
)

Grüße
Kai

--
This signature is left as an exercise for the reader.
Unsatz des Jahres:
$POLITIKER ruft $PARTEI zur Geschlossenheit.

Re: SQL-Abfrage für m:n-Verknüpfung

"Stefan Rybacki" schrieb im Newsbeitrag
news:3m5u5mF154mg4U1@individual.net...
> Stefan Gehrig wrote:
>> Hallo zusammen,
>>
>> irgendwie stehe ich gerade bei einer SQL-Abfrage auf dem Schlauch....
>> Folgendes ist gegeben:
>>
>> Tabelle fahrzeuge
>> ====
>> id (PK)
>> hersteller
>> typ
>>
>> Tabelle kategorien
>> ====
>> id (PK)
>> name
>>
>> Tabelle fahrzeugeKategorien
>> ===
>> fid (PK)
>> kid (PK)
>>
[...]
>>
>> Jetzt aber meine Frage: wie lautet die SQL-Abfrage für die Kategorien, zu
>> denen das Fahrzeug NICHT gehört?
>> Meine erste (für meinen Geschmack naheliegenste Lösung) wäre gewesen:
>> SELECT DISTINCT k.id, k.name
>> FROM kategorien k
>> LEFT JOIN fahrzeugeKategorien fk ON k.id=fk.kid
>> WHERE ISNULL(fk.fid) OR fk.fid!=? ORDER BY k.name
>> Aber die funktioniert natürlich nicht.
>>
>
> Versuche dies:
>
> SELECT DISTINCT k.id, k.name
> FROM kategorien k
> LEFT JOIN fahrzeugeKategorien fk ON k.id=fk.kid AND fk.fid=?
> WHERE ISNULL(fk.kid) ORDER BY k.name
>
Hi Stefan,

herzlichen Dank - das funktioniert perfekt!
Aber eine Frage noch: bist du sicher, daß das DISTINCT unbedingt nötig ist?

Schönen Gruß
Stefan

Re: SQL-Abfrage für m:n-Verknüpfung

Stefan Gehrig wrote:
>...
>
> Hi Stefan,
>
> herzlichen Dank - das funktioniert perfekt!
> Aber eine Frage noch: bist du sicher, daß das DISTINCT unbedingt nötig ist?

Nein, ist unnötig. Stammt auch nicht wirklich von mir, sondern von dir ;) Hatte deine
Anfrage einfach kopiert und nur die JOIN und WHERE Klauseln geändert.

Bis denn dann
Stefan


>
> Schönen Gruß
> Stefan
>
>

Re: SQL-Abfrage für m:n-Verknüpfung

"Stefan Rybacki" schrieb im Newsbeitrag
news:3m8usrF15m3aaU1@individual.net...
> Stefan Gehrig wrote:
>> Aber eine Frage noch: bist du sicher, daß das DISTINCT unbedingt nötig
>> ist?
>
> Nein, ist unnötig. Stammt auch nicht wirklich von mir, sondern von dir ;)
> Hatte deine Anfrage einfach kopiert und nur die JOIN und WHERE Klauseln
> geändert.
OK - dachte schon, ich hab deine Abfrage nicht ganz kapiert...
Das DISTINCT war in der Ursprungsabfrage mal nötig (dachte ich jedenfalls,
weil es kurzfristig mal zum richtigen Ergebnis geführt hat) - allerdings bin
ich dann ja drauf gekommen, daß die Abfrage komplett Schrott ist.

Danke nochmal!
Gruß
Stefan