MySQL Forums
Forum List  »  PHP

Re: Query divides movie titles into different columns
Posted by: Luigi Chiabrera
Date: November 14, 2019 06:25PM

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

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

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.