GROUP_CONCAT Mysql, MySQL

GROUP_CONCAT Mysql

DROP table tmdb_movies ;
DROP table recommendations;
CREATE TABLE tmdb_movies (
  tmdb_id INTEGER NOT NULL PRIMARY KEY,
  movie_title TEXT NOT NULL
);

INSERT INTO tmdb_movies (tmdb_id, movie_title) VALUES
(1, 'The Dark Knight Rises');

CREATE TABLE recommendations (
  recommendations_tmdb_id INTEGER NOT NULL,
  recommendations_title TEXT NOT NULL,
  recommendations_vote_average TEXT NOT NULL
);

INSERT INTO recommendations (recommendations_tmdb_id, recommendations_title, recommendations_vote_average) VALUES
(1, 'The Dark Knight Rises', '7.5'),
(1, 'Batman Begins', '7.5'),
(1, 'Iron Man', '7.3'),
(1, 'The Lord of the Rings: The Return of the King', '8.1'),
(1, 'The Lord of the Rings: The The Fellowship of the Ring', '8'),
(1, 'The Lord of the Rings: The Two Towers', '7.9'),
(1, 'The Matrix', '7.9'),
(1, 'Inception', '8'),
(1, 'Iron Man 2', '6.6'),
(1, 'Captain America: The First Avenger', '6.6');

SELECT tmdb_movies.movie_title
,GROUP_CONCAT(recommendations.recommendations_vote_average) as recommendations_vote_average
,GROUP_CONCAT(recommendations.recommendations_title) as recommendations_title
FROM tmdb_movies

LEFT JOIN recommendations ON recommendations.recommendations_tmdb_id=tmdb_movies.tmdb_id

Where tmdb_movies.tmdb_id=1

GROUP BY tmdb_movies.movie_title


run \| edit \| history \| help	0

Please log in to post a comment.