CODEWITHSUNDEEP
mysqlsql
Youandi Hossen
Youandi Hossen

Reputation: 1

MYSQL query table joins gives duplicate rows

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?

Upvotes: 0

Views: 50

Answers (2)

Yokki vallayok
Yokki vallayok

Reputation: 103

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

Upvotes: 1

StanislavL
StanislavL

Reputation: 57381

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

Upvotes: 0

Related Questions