Get Name from characters that died on Episode3 with its killer

I have a doubt on this sql problem.

Here is the tables.

I currenly have all the characters names that died on Episode 3, but I don’t know how to get the character killers.

Any idea?

Select name from characters c, deaths d where c.id = d.id_character and d.id_film= (Select id from films f where f.episode = "Episode III")

This is my sql query so far.

Answer

You can join the characters table twice:

select c.name, k.name as killer
from films f
inner join deaths d on f.id = d.id_film
inner join characters c on c.id = d.id_character 
inner join characters k on k.id = d.id_killer
where f.episode = 'Episode III'

Notes:

• Use standard joins! Implicit joins (with commas in the from clause) are legacy syntax from (literaly) decades ago, that should not be used in new code

• Use single quotes for literal strings; in standard SQL, double quotes stand for identifiers (although MySQL makes things differently)