MySQL Query question (group_concat)

Databases
1

Hello.
I’ve 2 tables:


movies
movie_id | movie_name          | movie_leadactor | movie_director
1        | Into the Wild       | 1               | 2
2        | Requiem for a Dream | 4               | 3

people
people_id | people_name      | people_isactor | people_isdir
1         | Emile Hirsch     | 1              | 0
2         | Sean Penn        | 0              | 1
3         | Darren Aronofsky | 0              | 1
4         | Ellen Burstyn    | 1              | 0

with query:

SELECT GROUP_CONCAT(p.people_name) AS people, m.movie_name
FROM people AS p
JOIN movies AS m
ON m.movie_leadactor = p.people_id 
OR m.movie_director = p.people_id 
GROUP BY m.movie_name

I get

people 	| movie_name
Emile Hirsch,Sean Penn  | 	Into the Wild
Darren Aronofsky,Ellen Burstyn 	| Requiem for a Dream

How to query to get always ‘Actor’ (its people_isactor) on first place and then on second place 'Director '(its people_isdir), because now for movie “Requiem…” ‘Director’ is on first place ?

2

Try adding ORDER BY people.people_isactor DESC to your query.

3

I changed query to

SELECT GROUP_CONCAT(p.people_name) AS people, m.movie_name
FROM movies AS m
JOIN (SELECT * 
        FROM people 
        ORDER BY people_isactor ASC) AS p
ON m.movie_leadactor = p.people_id 
OR m.movie_director = p.people_id 
GROUP BY m.movie_name

and now works. Thanks for suggestion.