finding opposite result using mysql IN

Question

I have two mysql tables, event_template and event_occurrence, where event_occurrence has a column with event_template id's. I want to find all event_templates which have no event_occurrence.

Trying this query I get 0 rows:

SELECT * FROM `event_template` where id NOT IN (SELECT event_template_id FROM event_occurrence)

Note that event_template has 166 rows, and 162 are returned by this query:

SELECT * FROM `event_template` WHERE id IN (SELECT event_template_id FROM event_occurrence)

I think you might be dealing with a `null` problem. See http://stackoverflow.com/q/129077; although that question is SQL Server specific, the `IN` and `NOT IN` clauses aren't. — Robert Harvey, Jul 16 '12 at 18:52
@RobertHarvey, ok seems quite knotty then, don't know if it's worth going into for me right now — Robin Manoli, Jul 16 '12 at 18:59

Ike Walker · Accepted Answer · 2012-07-16T19:06:36.263

0

You should use NOT EXISTS instead of NOT IN. This should work for you:

SELECT * 
FROM event_template et
WHERE NOT EXISTS (SELECT NULL FROM event_occurrence eo where eo.event_template_id = et.id)

edited Jul 16 '12 at 19:06

answered Jul 16 '12 at 19:01

Ike Walker

64,401
14
110
109

"SELECT * FROM event_template WHERE NOT EXISTS (SELECT NULL FROM event_occurrence where event_template_id = event_template.id)" (a slight change) works perfectly! – Robin Manoli Jul 16 '12 at 19:03
1

In that case I'm guessing you also have a column named ID in the event_occurence table. I'll update the query above to one that should work for that case. – Ike Walker Jul 16 '12 at 19:06

finding opposite result using mysql IN

1 Answers1