CODEWITHSUNDEEP
sqlsql-server
Johan de Klerk
Johan de Klerk

Reputation: 2535

Duplicate Rows on SQL Join

When I do a left join on a table it duplicates the results from the left table when more values are found in the right table. How can I only return the distinct values from the left even when there are multiple values on the right table. (return only the one movie even when there are multiple images on the right table)

SELECT
    *
FROM
    Movie AS M
    LEFT JOIN MovieImage AS MI WITH (
        NOLOCK
) ON M.TheMovieDbId = MI.TheMovieDbId
WHERE
    MI.ImageType = 'Poster'
    AND MI.ImageSize = 'Thumb'
    OR MI.ImageType IS NULL

Upvotes: 2

Views: 6144

Answers (2)

Asok
Asok

Reputation: 96

If you just want to pick one, why not just do a group by, and select min() or max()? Depending if you want to get all movies regardless if they have a image or not, use the left join. If you only want the movies that have images, use inner join. If you only want the movies that have a specific number of images, use the having clause.

Simplified example:

Select M.Name, isnull(max(MI.ImageName), 'No Image')
From Movie M
Left Join MovieImage MI
On M.TheMovieDbId = MI.TheMovieDbId
Group By M.Name

Upvotes: 1

gbn
gbn

Reputation: 432261

Typically, you wouldn't use a "right table" if you don't want results from it.

SELECT 
     *
FROM 
    Movie AS M
WHERE
    EXISTS (SELECT *
      FROM
          MovieImage AS MI
      WHERE  
          M.TheMovieDbId = MI.TheMovieDbId
          AND
          MI.ImageType = 'Poster'
          AND 
          MI.ImageSize = 'Thumb')

To have rows where no rows match too (as per your query), use this

SELECT 
     *
FROM 
    Movie AS M
WHERE
    EXISTS (SELECT *
      FROM
          MovieImage AS MI
      WHERE  
          M.TheMovieDbId = MI.TheMovieDbId
          AND
          MI.ImageType = 'Poster'
          AND 
          MI.ImageSize = 'Thumb')
UNION
SELECT * FROM Movie

Otherwise, you are saying "give me an arbitrary row from MovieImage" which I never understand...

Other notes:

  • don't use NOLOCK all the time
  • qualify your table names with schema name (typically dbo)

Upvotes: 3

Related Questions