Zoológico SQL
https://sqlzoo.net/wiki/More_JOIN_operaciones
N° 12:
Actor principal en las películas de Julie Andrews.
Indique el título de la película y el actor principal de todas las películas en las que actuó 'Julie Andrews'.
¿Recibiste "Little Miss Marker dos veces"?
Julie Andrews protagonizó la nueva versión de 1980 de Little Miss Marker y no la original (1934).
El título no es un campo único, cree una tabla de ID en su subconsulta (¿Qué significa esto?)
Lo que escribí fue esto pero dice que estoy equivocado, no sé por qué:
SELECT title, name
FROM (movie JOIN casting ON movie.id = casting.movieid) JOIN actor ON actor.id = casting.actorid
WHERE title IN
(SELECT title FROM casting JOIN actor ON casting.actorid = actor.id
where actor.name = 'Julie Andrews')
AND ord = 1
------------------------------------
WHERE title IN
(SELECT title FROM casting JOIN actor ON casting.actorid = actor.id
where actor.name = 'Julie Andrews')
No hay una columna de título ni en el reparto ni en el actor. entonces tienes accidentecorrelacionó claramente esta subconsulta: el título tiene un alcance externo a la subconsulta, por lo que efectivamente tiene algo como
where 1 in
(select 1
from casting JOIN actor ON casting.actorid = actor.id
where actor.name = 'Julie Andrews'
)
Lo cual será cierto si esa subconsulta devuelve alguna fila. Tenga siempre claro de qué fila debe provenir una columna.
Probablemente quisiste seleccionar la columna movieid del casting y usarla como filtro contra movie.id similar a tu unión en la primera subconsulta:
SELECT movie.title, actor.name
FROM movie
JOIN casting ON movie.id = casting.movieid
JOIN actor ON actor.id = casting.actorid
WHERE movie.id IN
(SELECT casting.movieId FROM casting JOIN actor ON casting.actorid = actor.id
where actor.name = 'Julie Andrews')
AND casting.ord = 1
------------------------------------
Lo intenté rápido, esto funciona
/* Create a derived table of the relevant IDs*/
with j as (
select movieid
from casting c join actor a on a.id=c.actorid
where a.name='julie andrews'
)
/*
Now use this as a starting point to join to the
relevant tables to fetch the required actors from
the movieIDs from the derived table
*/
select m.title, a.name
from j join movie m on m.id=j.movieid
join casting c on c.movieid=j.movieid
join actor a on a.id=c.actorid
where c.ord=1
------------------------------------
SELECT m.title AS film_title, a.name AS leading_actor
FROM movie m
JOIN casting c ON m.id = c.movieid
JOIN actor a ON c.actorid = a.id
WHERE m.id IN (
SELECT movieid
FROM casting
WHERE actorid = (
SELECT id
FROM actor
WHERE name = 'Julie Andrews'
)
)
AND c.ord = 1;
