Find mysql entries without match in other tables
I have two tables in mysql with the following structure:
Events: id Events_artists: event_id, more columns
I would like to find the event_ids in the table events_artists that do not have a match with the id in events.
The only thing I have come up with so far is this:
SELECT * FROM events,events_artists WHERE events_artists.event_id!=events.id
However, that is crap and basically returns the whole table.
Does anyone know how to solve this?
SOLUTION FOUND, thanks to Andrzej Bobak
select * from events_artists where event_id not in (select id from events)
How about this approach?
select * from events_artists where event_id is null or event_id not in (select id from events)
select * from events_artists a left join events b on a.id = b.id where b.id is null
your approach uses a cartesian product which joins every row with each other. So your where criteria just filters your result containing the rows that don't match, but that will be a lot because of the cartesian product