3

I'm working with PHP and MySql. I'm trying to find a way to select a number of movies from a mysql table, but apart from the movies table I have a watchlist table that stores a userID and the movieID of the movies he/she has added to his/her watchlist:

id        userID      movieID
=====================================
1          1            3
2          1            5
3          1            7
4          2            3
5          2            2
6          3            2

The movies table looks something like this

movieID    title    duration
=============================
1          tit1       34:43  
2          tit2       35:43   
3          tit3       24:43     
4          tit4       34:13   
5          tit5       11:43    
6          tit6       22:43    
7          tit7       33:43

The result I'm after is (for example for the user with ID 1):

movieID    title    duration   added
=======================================
1          tit1       34:43     false
2          tit2       35:43     false
3          tit3       24:43     true
4          tit4       34:13     false
5          tit5       11:43     true
6          tit6       22:43     false
7          tit7       33:43     true

Is there a way to join both the movies and the watchlist table to produce the desired result?

Thanks.

cesarcarlos
  • 1,271
  • 1
  • 13
  • 33

1 Answers1

2

You can get the required output by using a LEFT JOIN and then checking for a NULL in the join table.

SELECT m.*, IF(w.id IS NULL, 0, 1) AS added
FROM movies m 
LEFT JOIN watchlist w ON (m.movieID = w.movieID AND w.userID = 1)
GROUP BY m.movieID
fubar
  • 16,918
  • 4
  • 37
  • 43
  • This only selects the movies that have been added by user 1. I need to get all movies, showing false for those that the user has not added. – cesarcarlos Nov 29 '17 at 05:41
  • Oops. I've moved the `userID` condition onto the `JOIN`. Try that. – fubar Nov 29 '17 at 05:42