1:n Beziehung mit AND-Bedingungen abfragen

Hi Leute,

ich habe folgende Tabellen (vereinfacht):

CREATE TABLE `Film` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `Titel` varchar(45) collate latin1_german1_ci NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;

INSERT INTO `Film` (`ID`,`Titel`) VALUES 
 (1,'Titel 1'),
 (2,'Titel 2'),
 (3,'Titel 3');

CREATE TABLE `Film_Genre` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `FilmID` int(10) unsigned NOT NULL,
  `GenreID` varchar(45) collate latin1_german1_ci NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;

INSERT INTO `Film_Genre` (`ID`,`FilmID`,`GenreID`) VALUES 
 (1,1,'3'),
 (2,2,'1'),
 (3,2,'2');

Wenn ich jetzt alle Filme selektieren möchte, denen Genre 1 oder 2 zugeordnet ist, so ist das relativ einfach:

SELECT * FROM Film F
INNER JOIN Film_Genre FG ON F.ID = FG.FilmID
WHERE FG.GenreID = 1 OR FG.GenreID = 2;

Mein Problem ist jetzt eine Abfrage, bei der beide Genres vorhanden sein müssen, also AND-verknüpft.

WHERE FG.GenreID = 1 AND FG.GenreID = 2

funktioniert natürlich nicht.

Da die Query dynamisch mit PHP generiert wird, habe ich mir einen Mehrfachjoin (je nach übergebene Suchparameter) überlegt, der auch funktioniert:

SELECT * FROM Film F
INNER JOIN Film_Genre FG1 ON F.ID = FG1.FilmID
INNER JOIN Film_Genre FG2 ON F.ID = FG2.FilmID
WHERE FG1.GenreID = 1 AND FG2.GenreID = 2;

Bei diesen Mehrfachjoins wird aber erstmal das Kreuzprodukt gebildet (zumindest, wenn ich das WHERE testweise weglasse) und daher mache ich mir Gedanken über die Performance. Das Beispiel ist bewusst trivial gehalten, in Wirklichkeit müssen auf diese Art mehrere Tabellen abgefragt werden und dann halt noch je nach Suchparameter die zusätzlichen Mehrfachjoins - das kann so nicht gut sein.

Hat jemand eine bessere Lösung für die Abfrage?

[Mr Ad]

[fab]

Wahrscheinlich auch noch nicht optimal aber spontan würde ich es schonmal so ändern:

SELECT * FROM Film F
LEFT JOIN Film_Genre FG1 ON F.ID = FG1.FilmID AND FG1.GenreID = 1
LEFT JOIN Film_Genre FG2 ON F.ID = FG2.FilmID AND FG2.GenreID = 2
WHERE FG1.GenreID = 1 AND FG2.GenreID = 2;

[ChrisB]

WHERE genre = x OR genre = y in Kombination mit GROUP BY und HAVING COUNT() = 2 kann man für so etwas ebenfalls benutzen. (Die Anzahl an Datensätzen, die man per HAVING verlangt, muss dann allerdings auch schon vorher im Script ermittelt und eingesetzt werden.)

[hts]

Ich danke euch.
Mit gruppieren kam mir vorhin auch den Sinn, ich hab's mal probiert. Also das funktioniert auch:

SELECT * FROM Film F
INNER JOIN Film_Genre FG ON F.ID = FG.FilmID
WHERE FG.GenreID = 1 OR FG.GenreID = 2
GROUP BY F.ID
HAVING COUNT(F.ID) = 2;

Nur handelt es sich wie gesagt um mehrere Tabellen, die sich auf eine Basis-Tabelle beziehen. Ich simuliere diese jetzt mal mit einen SELF-JOIN (Tabelle 1 und 2):

SELECT * FROM Film F
INNER JOIN Film_Genre Tabelle1 ON F.ID = Tabelle1.FilmID
INNER JOIN Film_Genre Tabelle2 ON F.ID = Tabelle2.FilmID
WHERE Tabelle1.GenreID = 1 OR Tabelle1.GenreID = 2 OR Tabelle2.GenreID = 1
GROUP BY F.ID
HAVING COUNT(F.ID) = 3;

Das bringt kein Ergebnis, da COUNT durch den weiteren Join jetzt 4 und nicht 3 ergibt Außerdem fällt mir gerade ein, dass der Suchparameter (im Beispiel GenreID) theoretisch auch mehrere Einträge enthalten kann.

Also könnte ich das dann mit HAVING COUNT(F.ID) >= 3 erschlagen?
Und wenn die Parameter ODER-verknüpft gesucht werden sollen, dann >= 1.

Hm, ganz schön kompliziert, muss ich nochmal drüber nachdenken ...

PS: Eine beliebige Schachtelung von UND-/ODER-Verknüpfungen soll auch möglich sein.

[hts]

Hi Leute,

wollte nur nochmal mein Feedback geben.

Ich habe es jetzt so in etwa (ein wenig komplexer war es schon) SQL-mäßig umgesetzt und voriger Woche den Kunden vorgestellt, die begeistert waren. Darum habe ich auch diese Woche verdient Urlaub

Es können jetzt beliebige Abfragen über alle Tabellen mit beliebiger Schachtelung (UND/ODER) und Tiefe (ok, bei 64 Joins wird eine Exception geschmissen) gefahren werden.

Vor allem die "einfache" GUI-Umsetzung war dabei eine Herausforderung. Ich mache jetzt drei Kreuze ^^