Nur eine Idee: beide Serien einzeln hinzujoinen.
SQL-Code:
SELECT
PER.Name, P1.Role, P2.Role
FROM
PERSON PER
JOIN
PLAYS P1 ON P1.PERSON = PER.person
JOIN
PLAYS P2 ON P2.Person = PER.person
JOIN
EPISODE E1 ON E1.SERIES = P1.PRODUCTION
JOIN
EPISODE E2 ON E2.SERIES = P2.PRODUCTION
JOIN
SERIES S1 ON S1.SERIES = E1.SERIES
JOIN
SERIES S2 ON S2.SERIES = E2.SERIES
WHERE
S1.Title = 'The Big Bang Theory'
AND
S2.Title = 'Desperate Housewives'
Ungetestet
Ich konnte mich damit jetzt nochmal beschäftigen.
Zwischen EPISODE und PLAYS ist der Schlüssel PRODUCTION. Als mir das aufgefallen ist, hat es geklappt.
SQL-Code:
SELECT
PER.Name, P1.Role, P2.Role
FROM
PERSON PER
JOIN
PLAYS P1 ON P1.PERSON = PER.PERSON
JOIN
PLAYS P2 ON P2.Person = PER.PERSON
JOIN
EPISODE E1 ON E1.PRODUCTION= P1.PRODUCTION
JOIN
EPISODE E2 ON E2.PRODUCTION= P2.PRODUCTION
JOIN
SERIES S1 ON S1.SERIES = E1.SERIES
JOIN
SERIES S2 ON S2.SERIES = E2.SERIES
WHERE
S1.Title = 'The Big Bang Theory'
AND
S2.Title = 'Desperate Housewives'
Also hier der obligatorische Dank für die Hilfe.
