Return only unique table id's from MySQL join

Question

I have a MySQL database with two tables (simplified for this question):
movies table columns: id, title, duration, description, created_at, updated_at
rentals table columns: id, movie_id, status, created_at, updated_at

The rental status is either 'CHECKED OUT' or 'RETURNED', so a movie is available if it either has no associated rental, or all of its associated rentals have a status of 'RETURNED'. How do I query available movies? Right now I've got this:

SELECT rentals.id,rentals.status,movies.* 
FROM `movies` 
LEFT OUTER JOIN rentals ON movies.id = rentals.movie_id 
        AND movies.kiosk_id = rentals.kiosk_id 
WHERE (`movies`.kiosk_id = 1 
        AND (rentals.id is null or rentals.status != 'CHECKED OUT'));

The problem is this query returns multiple records for a movie if it's been checked out several times, so my ORM gives me duplicate movie objects. Is there some way to specify that I only want unique movie.id's? Should I have some different database schema? Or should I sort it out programatically (seems like it would be too slow)?

Answer 1

I think you want the distinct rentals.id in your query. try this:

SELECT DISTINCT rentals.id,rentals.status,movies.* 
FROM `movies` 
LEFT OUTER JOIN rentals ON movies.id = rentals.movie_id 
        AND movies.kiosk_id = rentals.kiosk_id 
WHERE (`movies`.kiosk_id = 1 
        AND (rentals.id is null or rentals.status != 'CHECKED OUT'));

Answer 2

I'm not sure what you want with the rentals id, so I took it out.

SELECT
  rentals.status, movies.*
FROM movies
LEFT OUTER JOIN (
  SELECT
    movie_id,
    kiosk_id,
    -- Because 'CHECKED OUT' comes alphabetically before 'RETURNED',
    -- this will have 'CHECKED OUT' if there's at least one rental checked out
    MIN(status) AS status
  FROM rentals
  GROUP BY movie_id, kiosk_id) rentals
ON movies.id = rentals.movie_id
AND movies.kiosk_id = rentals.kiosk_id
WHERE movies.kiosk_id = 1
AND (rentals.id is null or rentals.status != 'CHECKED OUT');

Answer 3

Why do a LEFT OUTER JOIN? A simple LEFT JOIN should suffice.

You should also move the rentals.status to the ON() bit (I think):

SELECT DISTINCT movies.id, movies.*, rentals.id, rentals.status
FROM `movies` 
LEFT JOIN rentals ON (movies.id = rentals.movie_id AND movies.kiosk_id = rentals.kiosk_id AND rentals.status != 'CHECKED OUT')
WHERE `movies`.kiosk_id = 1 AND rentals.id is null

//Edit You'll also need a distinct

Answer 4

Your problem that you want a result that makes no sense. You want available movies -- that's ok, but what would a rental id mean in the same row with some movie id? You should throw away rentals in SELECT list and after that applying DISTINCT to just movies.id will do the trick

Answer 5

If you need all available movies for rental you can try this: SELECT movies.* FROM movies LEFT JOIN ( SELECT DISTINCT movie_id FROM rentals WHERE status!='CHECKED OUT' AND kiosk_id=1) r ON movies.movie_id=r.movie_id