Search code examples
mysqlsqlsubquery

Getting data from one table if not also in another

I've got a database with two relevant tables (times and registrations).

I want to get the times from the times table, through an SQL query, that aren't in the registrations table (don't have a registration on them). Registration at 5PM, wouldn't show 5PM from times in the query.

My current query is:

SELECT time FROM `times` WHERE time IN (SELECT time FROM `registrations` WHERE ID IS NOT NULL)

(registrations DO have an ID)

This does the opposite of what I want it to do (shows all times, regardless of registration, or not). How could I get the opposite effect?

Solution

  • You seem to want not exists:

    select t.*
from times t
where not exists (select 1 from regisrations r where r.time = t.time)