mysql select where not equal value from another table-CodePudding

I have the following tables; image, user, vote

fields:

image -> id, total, status

vote -> user_id, image_id, vote

user -> id, name, pass, status

When a user votes on an image the 'vote' table will update with their user_id, the image they voted on (image_id) and their 'vote'.

I want to query the table to show me all images that the current user has NOT voted for. Here is my query:

SELECT * FROM image, vote WHERE vote.image_id != image.id

CodePudding user response：

SELECT 
  * 
FROM image
WHERE 
  id NOT IN (
    SELECT image_id FROM vote WHERE user_id = USER_ID_HERE
  )

or:

SELECT 
  image.*
FROM image
LEFT JOIN vote ON (
  vote.image_id = image.id AND vote.user_id = USER_ID_HERE
) 
WHERE vote.user_id IS NULL