SQL query to check if value doesn't exist in another table

Question

I have a SQL query which does most of what I need it to do but I'm running into a problem.

There are 3 tables in total. entries, entry_meta and votes.

I need to get an entire row from entries when competition_id = 420 in the entry_meta table and the ID either doesn't exist in votes or it does exist but the user_id column value isn't 1.

Here's the query I'm using:

SELECT entries.* FROM entries 
INNER JOIN entry_meta ON (entries.ID = entry_meta.entry_id)
WHERE 1=1 
    AND ( ( entry_meta.meta_key = 'competition_id' AND CAST(entry_meta.meta_value AS CHAR) = '420') ) 
GROUP BY entries.ID 
ORDER BY entries.submission_date DESC 
LIMIT 0, 25;

The votes table has 4 columns. vote_id, entry_id, user_id, value.

One option I was thinking of was to SELECT entry_id FROM votes WHERE user_id = 1 and include it in an AND clause in my query. Is this acceptable/efficient?

E.g.

AND entries.ID NOT IN (SELECT entry_id FROM votes WHERE user_id = 1)

Have you tried it? It looks reasonable, although you might want to read about [NOT EXISTS vs NOT IN](http://stackoverflow.com/questions/2246772/whats-the-difference-between-not-exists-vs-not-in-vs-left-join-where-is-null) — Andrew, Jul 14 '14 at 18:29
Works perfectly! Thanks Andrew. Sometimes actually writing the problem down in order to explain it clarifies the solution :) — Nathan Dawson, Jul 14 '14 at 18:35

score 0 · Answer 1 · answered Jul 14 '14 at 18:50

A left join with an appropriate where clause might be useful:

SELECT 
    entries.* 
FROM 
    entries 
    INNER JOIN entry_meta ON (entries.ID = entry_meta.entry_id)
    LEFT JOIN votes ON entries.ID = votes.entry_id
WHERE 1=1 
  AND (
      entry_meta.meta_key = 'competition_id' 
      AND CAST(entry_meta.meta_value AS CHAR) = '420') 
      AND votes.entry_id IS NULL -- This will remove any entry with votes
  ) 
GROUP BY entries.ID 
ORDER BY entries.submission_date DESC

score 0 · Answer 2 · answered Jul 14 '14 at 19:05

Here's an implementation of Andrew's suggestion to use exists / not exists.

select
  e.*
from
  entries e
  join entry_meta em on e.ID = em.entry_id
where
  em.meta_key = 'competition_id'
  and cast(em.meta_value as char) = '420'
  and (
    not exists (
      select 1
      from votes v
      where
        v.entry_id = e.ID
    )
    or exists (
      select 1
      from votes v
      where
        v.entry_id = e.ID
        and v.user_id != 1
    )
  )
group by e.ID
order by e.submission_date desc
limit 0, 25;

Note: it's generally not a good idea to put a function inside a where clause (due to performance reasons), but since you're also joining on IDs you should be OK.

Also, The left join suggestion by Barranka may cause the query to return more rows than your are expecting (assuming that there is a 1:many relationship between entries and votes).

SQL query to check if value doesn't exist in another table

2 Answers2