How to include Null values in Join-CodePudding

I am working on an assignment which needs me to generate a list of films showing name and year and actor number that have the word "JEDI" in the title. In the actor number column only show actor number 3 if the movie has actor 3 in them, otherwise show null. Order by film title.

My current code is as follows:

select film_actor.film_id, title, actor_id from film_actor
left join film
on film.film_id = film_actor.film_id
where actor_id in (select actor_id from film_actor where actor_id = "3")
and title like "%JEDI%"
group by film_id
order by title

This outputs only the result that matches where actor_id = "3", but it doesn't show the two films where "JEDI" is in the title but actor_id is not 3. This image shows the desired result on the right, and my result on the left

What can I change in my code to get to the desired result?

Thank you very much!

CodePudding user response：

You need to make film the parent table, and actor the table you left join with, since that's the one that may not have a matching element (you could also use RIGHT JOIN, but that's less common).

And then you need to put the criteria of the actor table into the ON condition, not WHERE.

SELECT film.film_id, film.title, film_actor.actor_id
FROM film
LEFT JOIN film_actor
ON film.film_id = film_actor.film_id AND film_actor.actor_id = 3
WHERE title LIKE '%JEDI%'
ORDER BY title

There's no need for GROUP BY here.

CodePudding user response：

Your left join is backwards for your purpose, since you want all matching films but only the actor when there is one:

select film_actor.film_id, title, actor_id
from film
left join film on film.film_id = film_actor.film_id and film_actor.actor_id = "3"
where title like "%JEDI%"
order by title

No group by is necessary if there is at most one actor_id 3 per film