oracle sql anfrage, 2 bedingungen

[Jonas Shinaniganz]

Zitat von DeddyH:

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.