Re: Query divides movie titles into different columns
updating
I structured the query in this way, but it returns all the players in the database in the cast of the two films with doubled values
select
actor_id,
count(*) as film,
(select name from actor a where a.actor_id = am.actor_id) as name,
(select act_img from actor a where a.actor_id = am.actor_id) as act_img,
(select group_concat(title SEPARATOR ';')) as title
from film f, film_actor am
where f.film_id in (select film_id from film_actor where actor_id = 1)
group by actor_id
order by count(*) desc;
# actor_id, film, name, act_img, GROUP_CONCAT(title SEPARATOR ';')
3, 6, Max von Sydow, m_vsydow.jpg, Exorcist-I;Exorcist-II;Exorcist-I;Exorcist-II;Exorcist-I;Exorcist-II
1, 4, Linda Blair, l_blair.jpg, Exorcist-I;Exorcist-II;Exorcist-I;Exorcist-II
43, 4, Kitty Winn, k_winn.jpg, Exorcist-I;Exorcist-I;Exorcist-II;Exorcist-II
2, 2, Ellen Burstyn, e_burstyn.jpg, Exorcist-I;Exorcist-II
4, 2, Jason Miller, j_miller.jpg, Exorcist-I;Exorcist-II
5, 2, William O'Malley, w_omalley.jpg, Exorcist-I;Exorcist-II
6, 2, Lee J.Cob, l_jcob.jpg, Exorcist-I;Exorcist-II
7, 2, Eileen Dietz, e_dietz.jpg, Exorcist-I;Exorcist-II
8, 2, Mercedes McCambridge, m_mcambridge.jpg, Exorcist-II;Exorcist-I
9, 2, Ron Faber, r_faber.jpg, Exorcist-I;Exorcist-II
...........
61, 2, John Dair, j_dair.jpg, Exorcist-I;Exorcist-II
62, 2, Christopher Fairbank, c_fairbank.jpg, Exorcist-II;Exorcist-I
63, 2, Kit Hollerbach, k_hollerbach.jpg, Exorcist-I;Exorcist-II
64, 2, Hugo Blick, h_eblick.jpg, Exorcist-I;Exorcist-II
65, 2, Charkes Roskilly, c_roskilly.jpg, Exorcist-II;Exorcist-I
66, 2, David Baxt, d_baxt.jpg, Exorcist-I;Exorcist-II
67, 2, Sharon Holm, s_holm.jpg, Exorcist-I;Exorcist-II