SQL Query Problem...

template

Hi,

gegeben ist folgende Strutkur:

[Filme] (id, name)
[Darsteller] (id, name)
[Besetzung] (id, filmid, darstellerid)

Wie kann ich halbwegs effizent nach einem film suchen, in dem mehrere gegebene Darsteller mitgespielt haben. Also ich möchte z.B. alle Filme haben in denen die Darsteller mit den ids 815, 4711, 1234, 9876 und 1928 mitgespielt haben. Wie mache ich das?

So nach dem Motto:
SELECT id, name FROM Filme JOIN Besetzung ON Filme.id = Besetzung.filmid WHERE Besetzung.darstellerid = 815 OR Besetzung.darstellerid = 4711....

Aber so gehts ja logischerweise nicht, da jeder darsteller mit jedem film in einer eigenen Zeile gejoint wurde.

Vielen dank im voraus
template

CStoll

Um nach zwei Schauspielern zu suchen, müßtest du zwei Ausgaben von "Besetzung" parallel durchlaufen:

SELECT f.name
FROM Filme AS f INNER JOIN (Besetzung AS b1 INNER JOIN BESETZUNG AS b2 ON b1.filmid=b2.filmid) ON f.id=b1.filmid
WHERE b1.darstellerid=815 AND b2.darstellerid=4711;

Für drei oder mehr Darsteller wird dein Join entsprechend länger.

Finten

SELECT f.name
FROM filme f,
     besetzung b
WHERE f.id = b.filmid
AND b.darstellerid IN (815, 4711, 1234, 9876, 1928)

Edit:Wenn du die Ausgabe doppelter Datensätze vermeiden willst, schreibe "DISTINCT" zwischen "SELECT" und "f.name"

template

Danke euch beiden für die Antwort

@CStoll
Ja, das mit mehreren Joins hatte ich auch als Grundüberlegung aber für jeden join kommt doch normalerweise eine finaltablle mit einer Zeilenanzahl der Tabelle * der Anzahl der Join Tabelle. Da kommen ja unglaublich große Datenmengen bei raus, wenn ich da mal ein bischen rechne:

Filme: ca. 1000 und ich sage jetzt einfach mal ungefähr 40 Darsteller pro Film

1. Join 1000 * 40 = 40.000
2. Join 40000 * 40 = 1.600.000
... = 64.000.000
... = 2.560.000.000

Da kann einem ja schwindlig werden;) Gut natürlich muss das Datenbanksystem diese Monstertabelle nicht wirklich erstellen und löst das wahrscheinlich irgendwie anders, aber bleibt da wirklich eine halbwegs lineare effizienz bei weiteren Darstellern?

@Finten
Ganz verstehen tu ichs noch nicht aber ich werds heute Abend mal ausprobieren. Danke.

CStoll

Du kannst dir auch eine Zwischentabelle erstellen, die zu jedem Film die Liste aller Darsteller enthält (auch wenn mir da keine Aggregatfunktion einfällt, die das machen würde). Oder du filterst die einzelnen Zwischentabellen vor, so daß jede nur noch die Filme eines Darstellers enthält.

Finten

Hatte deine Frage falsch verstanden. Du suchst also Filme, wo Person X _und_ Person Y mitgespielt haben? Meine Abfrage sucht Filme, wo Person X _oder_ Y mitgewirkt haben.

Finten

Wenn alle Darsteller in einem Film mitgewirkt haben sollen, würde ich so vorgehen:
Die Darsteller, die mitgewirkt haben sollen in eine Tabelle schreiben (Suchvorgaben; darstellerid) und folgende Query benutzen:

SELECT f.name 
FROM filme f
WHERE NOT EXISTS (
	SELECT *
	FROM suchvorgaben sv2
	WHERE NOT EXISTS (
		SELECT *
		FROM besetzung b3
		WHERE b3.darstellerid = sv2.darstellerid
		AND b3.filmid = f.id
	)
)

ninguno

möglich wäre:

select filmid from (select count(*) cnt, filmid from besetzung where darstellerid in(815,4711) group by filmid) where cnt=2

oder sonst:

select * from filme f where exists (select null from besetzung where filmid=f.id and darstellerid=815) 
                        and exists (select null from besetzung where filmid=f.id and darstellerid=4711);