2

Here is the 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

That's all the information needed, i guess. Please let me know, if you need more information or want me to explain again.

Edit: Dear @Shadow, who marked this suggestion as duplicate. If I use DISTINCT then, not all records will display. Because it is possible that 2 movies have an average rating of 7.5

Toby
  • 657
  • 1
  • 6
  • 18
  • It's hard to understand your tables. So `cast.cast_tmdb_id` is actually not a cast ID, but a movie ID (you should change the column name to make this clear). Can I assume the same for `recommendations.recommendations_tmdb_id`? So that this is not a recommendation ID, but again the movie ID? Then you have a cast for a movie and recommendations. Your query shows recommendations. Why do you join the cast table then? – Thorsten Kettner Jul 06 '17 at 06:35
  • Please show your expected result. – Thorsten Kettner Jul 06 '17 at 06:36
  • On a sidenote: "And yes, I need group_concat to display the records together in my PHP code": So you are using a special PHP dialect that lacks loops and string concatenation? – Thorsten Kettner Jul 06 '17 at 06:38
  • Sir, i said this in the question that, I just want a record (cell) to echo only one time. See the fiddle, it echo the same movie name like 5-10 times. – Toby Jul 06 '17 at 06:42
  • And If i do not use group_concat, this is how the record will echo (i am showing movie name + cast name here) `Logan - Hugh Jackman Logan - Patrick Stewart Logan - Dafne Keen` And group_concat make them echo like this (this is what i want) `Logan - Hugh Jackman, Patrick Stewart, Defne Keen` – Toby Jul 06 '17 at 06:44
  • Well, I found one solution. I am using `If` statement to echo only first 10 records, that was easy. But i wanted a solution from MySQL query. But it's not a problem right? – Toby Jul 06 '17 at 06:51
  • The recommendations are shown multiple times, because you join with cast, so you show each recommendation once for each cast member. So either remove the join to the cast table or aggregate before joining, not afterwards. I've posted an answer showing how. – Thorsten Kettner Jul 06 '17 at 07:05

1 Answers1

0

Your question is not really clear. That is because you have not shown the desired result.

In your query you are selecting a column of recommended movie titles and another with votes. However, these are not related, as you didn't specify an order. So the first recommended movie may have the fifth vote shown for instance. And even if the votes were sorted by movie, that would still be hard to read.

So start with how the result would ideally look like. Only then write the query.

Example:

Expected result:

movie_title       | roles                          | recommendations
------------------+--------------------------------+------------------------
The Dark Knight   | Christian Bale (Bruce Wayne /  | The Dark Knight Rises (7.5),
                  | Batman), Michael Caine (Alfred | Batman Begins (7.5), Iron 
                  | Pennyworth), ...               | Man (7.3), ...

So we need an aggreation from the cast table (all roles per movie) and one from the recommendation table (all recommendations per movie). Do the aggregations first and then join the results:

SELECT 
  m.movie_title,
  c.roles,
  r.movies as recommendations
FROM tmdb_movies m
LEFT JOIN
(
  SELECT 
    cast_tmdb_id as movie_id,
    GROUP_CONCAT(CONCAT(cast_name, ' (', cast_character, ')') SEPARATOR ', ') as roles
  FROM cast
  GROUP BY cast_tmdb_id
) c ON c.movie_id = m.tmdb_id
LEFT JOIN
(
  SELECT 
    recommendations_tmdb_id as movie_id,
    GROUP_CONCAT(CONCAT(recommendations_title, ' (', recommendations_vote_average, ')')
                 SEPARATOR ', ') as movies
  FROM recommendations
  GROUP BY recommendations_tmdb_id
) r ON r.movie_id = m.tmdb_id
WHERE m.tmdb_id = 1;

Here is the rextester link: http://rextester.com/FHA48503

Thorsten Kettner
  • 69,709
  • 4
  • 37
  • 58