MYSQL query table joins gives duplicate rows

Question

I have the tables movie, movie_client, language_movie, language, subtitle_movie and subtitle

I want to select all movies with their subtitle and language where client_id (in movie_client) is 1

        SELECT * FROM movie 
        LEFT OUTER JOIN movie_client 
        ON movie.movie_id = movie_client.movie_client_id 
        LEFT OUTER JOIN client
        ON movie_client.client_movie_id = client.client_id

        LEFT OUTER JOIN language_movie
        ON movie.movie_id = language_movie.movie_id
        LEFT OUTER JOIN language
        ON language_movie.language_id = language.language_id

        LEFT OUTER JOIN subtitle_movie
        ON movie.movie_id = subtitle_movie.movie_id
        LEFT OUTER JOIN subtitle
        ON subtitle_movie.subtitle_id = subtitle.subtitle_id

        WHERE client.client_id=1

this does nog work cause i get duplicate rows i tried inner joins as swell but it just wont work. Can anyone help me with the right query?

Move the `client.client_id=1` condition to the it's `ON` clause to get true left join behavior. As it is now you get regular inner join result. — jarlh, Mar 11 '16 at 13:00
If a movie has subtitles in two languages, or is in two languages, you will get that movie twice. It's the expected result. If you want each movie only oncy, you have to describe how to chose language, subtitle etc. — jarlh, Mar 11 '16 at 13:02

score 1 · Answer 1 · answered Mar 11 '16 at 13:59

Give it a try :

    SELECT distinct m.movie_id, lm.language_id, sm.subtitle_id FROM movie m
    INNER JOIN movie_client mc
    ON m.movie_id = mc.movie_client_id 
    INNER JOIN client
    ON mc.client_movie_id = c.client_id

    INNER JOIN lm
    ON m.movie_id = lm.movie_id
    INNER JOIN language l
    ON lm.language_id = l.language_id

    INNER JOIN subtitle_movie sm
    ON m.movie_id = sm.movie_id
    INNER JOIN JOIN subtitle s
    ON sm.subtitle_id = s.subtitle_id

    WHERE client.client_id=1

score 0 · Answer 2 · edited May 23 '17 at 11:59

0

This

    FROM movie 
    LEFT OUTER JOIN movie_client 
    ON movie.movie_id = movie_client.movie_client_id 
    LEFT OUTER JOIN client
    ON movie_client.client_movie_id = client.client_id

should be INNER JOINs you don't need all movies

    SELECT * FROM movie 
    INNER JOIN movie_client 
    ON movie.movie_id = movie_client.movie_client_id 
    INNER JOIN client
    ON movie_client.client_movie_id = client.client_id

    LEFT OUTER JOIN language_movie
    ON movie.movie_id = language_movie.movie_id
    LEFT OUTER JOIN language
    ON language_movie.language_id = language.language_id

    LEFT OUTER JOIN subtitle_movie
    ON movie.movie_id = subtitle_movie.movie_id
    LEFT OUTER JOIN subtitle
    ON subtitle_movie.subtitle_id = subtitle.subtitle_id

    WHERE client.client_id=1

Check this to understand difference INNER and OUTER JOIN

edited May 23 '17 at 11:59

Community

1
1

answered Mar 11 '16 at 12:37

StanislavL

56,971
9
68
98

as @jarlh stated if movie has 2 languages or subtitle has 2 languages you get rows doubled. Which language should be left if there are 2? – StanislavL Mar 11 '16 at 13:13
yes i know but i get the same result from everything twice so one movie with one language twice the output – Youandi Hossen Mar 11 '16 at 13:18
@YouandiHossen, Can you add some sample table data, the current result, and the wanted result? – jarlh Mar 11 '16 at 13:24
Try to remove the OUTER JOINs one by one till duplicates exist to figure out which table provides the duplicates. – StanislavL Mar 11 '16 at 13:28
current result: client = 1 movie_id = 1 movie = 'movie1' language_id=1 language = ENG client = 1 movie_id = 1 movie = 'movie1' language_id=2 language = NED client = 1 movie_id = 1 movie = 'movie1' language_id=1 language = ENG client = 1 movie_id = 1 movie = 'movie1' language_id=2 language = NED Wanting:: client = 1 movie_id = 1 movie = 'movie1' language_id=1 language = ENG client = 1 movie_id = 1 movie = 'movie1' language_id=2 language = NED – Youandi Hossen Mar 11 '16 at 13:29
It goes wrong by adding the language without it works when i add those the results duplicate – Youandi Hossen Mar 11 '16 at 13:32
You can add ON language_movie.language_id = language.language_id and language.language_id=1 to leave just one – StanislavL Mar 11 '16 at 14:05
If you need bioth languages then you correctly get 2 rows with one movies but 2 languages. If you still need one row choose which language to see or define somehow rule how to select necessary language – StanislavL Mar 11 '16 at 14:33

MYSQL query table joins gives duplicate rows

2 Answers2