0

Note: Using DISTINCT will not solve the problem. (I will explain why)

Here is my code

DROP TABLE tmdb_movies;
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');

DROP TABLE recommendations;
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');

DROP TABLE cast;
CREATE TABLE cast (
  cast_tmdb_id INTEGER NOT NULL,
  cast_name TEXT NOT NULL,
  cast_character TEXT NOT NULL
);

INSERT INTO cast (cast_tmdb_id, cast_name, cast_character) VALUES
(1, 'Christian Bale', 'Bruce Wayne / Batman'),
(1, 'Michael Caine', 'Alfred Pennyworth'),
(1, 'Heath Ledger', 'The Joker'),
(1, 'Aaron Eckhart', 'Harvey Dent / Two-Face'),
(1, 'Gary Oldman', 'Lt. James Gordon'),
(1, 'Maggie Gyllenhaal', 'Rachel Dawes'),
(1, 'Morgan Freeman', 'Lucius Fox'),
(1, 'Chin Han', 'Lau'),
(1, 'Eric Roberts', 'Salvatore Maroni'),
(1, 'Monique Gabriela Curnen', 'Ramirez');



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

FROM tmdb_movies  

LEFT JOIN cast ON cast.cast_tmdb_id=tmdb_movies.tmdb_id
LEFT JOIN recommendations ON recommendations.recommendations_tmdb_id=tmdb_movies.tmdb_id

Where tmdb_movies.tmdb_id= 1

GROUP BY tmdb_movies.movie_title

Complete SQL Fiddle

As you can see, the records from recommendations table are displaying multiple times. But If i remove this line LEFT JOIN cast ON cast.cast_tmdb_id=tmdb_movies.tmdb_id then, they do not display multiple times. So this line is the main problem.

I did not use cast table in my SQL Query, just for the sake of simplicity. But I need it.

And yes, I need group_concat to display the records together in my PHP code

Why DISTINCT will not solve the problem? Well, some movies have the same rating, for example: 7.5. So, not all movie's rating will get displayed. That's why, you can try it yourself, try to use in the fiddle

QL

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Josh Poor
  • 505
  • 1
  • 5
  • 12
  • 4
    Why did you make a new question when you have several extensive answers in your other question? – gview Jul 06 '17 at 05:30
  • 2
    Didn't you [just ask this same question](https://stackoverflow.com/questions/44940170/how-to-stop-some-records-displaying-multiple-times-in-sql-query) using a different account? – Tim Biegeleisen Jul 06 '17 at 05:31
  • 1
    @TimBiegeleisen It's worse than you thought. This was the original on the same "other account". https://stackoverflow.com/questions/44931926/echo-mysql-data-using-group-concat-in-php – gview Jul 06 '17 at 05:36
  • 1
    Did you look at my answer in the other question, or the sqlfiddle? – gview Jul 06 '17 at 05:39
  • Guys, Me and Toby are working on this project together. Toby told me to ask it from my account, because Shadow locked that topic unfairly. (It is not duplicate). Thanks guys – Josh Poor Jul 06 '17 at 05:47
  • To a rough approximation, there is no problem in SQL for which GROUP_CONCAT need constitute any part of the answer. I cannot see that this will be the exception. – Strawberry Jul 06 '17 at 07:20

1 Answers1

2

Are you looking like for this result?

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

FROM tmdb_movies  

LEFT JOIN cast ON cast.cast_tmdb_id=tmdb_movies.tmdb_id
LEFT JOIN recommendations ON recommendations.recommendations_tmdb_id=tmdb_movies.tmdb_id

Where tmdb_movies.tmdb_id= 1

GROUP BY tmdb_movies.movie_title
kamesh pathak
  • 101
  • 7
  • No bro. As I said in the question above, If i use DISTINCT in recommendations_vote_average column. Then, only 6 column will appear. Because some movies have the same vote_average – Josh Poor Jul 06 '17 at 06:24