Display a list of fields, reporting movies and associates using CROSS JOIN

Question

I have three tables, the tb_film table, the tb_actor and the tb_ator_film, I need a script that displays the list of all the actors and films with them associated, follow images to clarify

And I need a script that displays them like this, used CROSS JOIN

Answer 1

Are you sure it’s cross Join you need? cross Join makes no connection between the tables. And you have the links. Try this query:

select f.id_filme, f.nome_filme, a.id_ator, a.nome_ator
from tb_filme f join tb_ator_filme af
on (f.id_filme=af.id_filme)
join tb_ator a
on (a.id_ator = af.id_ator)

Ps: I don’t have now with me BD to test, but I think it does what it needs