I have a table, movies.
---- ------
| id | name |
---- ------
And I have another table, genres.
---- ------ ----------
| id | name | movie_id |
---- ------ ----------
Guess, that every movie record have 3 genre records binded by movie_id. I need to get 10 movie records with ALL genre records joined.
My query is:
select *
from movies
left join genres on movies.id = genres.movie_id
limit 10;
Result is 10 rows, but I want to get 10 movie rows with ALL genre rows joined = 30 rows.
What query should be?
CodePudding user response：
you can select only 10 movies in a subquery:
select * from
(select * from movies limit 10) movies1
left join genres on movies1.id = genres.movie_id
CodePudding user response：
Using GROUP BY you can retrieve one row pre movie with concatinated genres:
select movies.id, movies.name, GROUP_CONCAT(genres.name) genres
from movies
left join genres on movies.id = genres.movie_id
group by movies.id, movies.name
limit 10;