sql problem with WHERE ('field' <> value)

Question

I have two sql queries, one which retrieves all the Event records for a given topic id and another which is supposed to retrieve all Event records excluding given topic id, but doesn't.

The first query retrieves the correct records

SELECT `events`.* 
FROM `events` INNER JOIN events_topics ON events.id = events_topics.event_id
WHERE (events_topics.topic_id = 75)

The second query which is supposed to exclude events does not exclude any.

SELECT `events`.* 
FROM `events` INNER JOIN events_topics ON events.id = events_topics.event_id 
WHERE (events_topics.topic_id <> 75)

So, the 2nd query gives events that where topic_id = 75 and it shouldn't? — gbn, Jun 26 '11 at 13:19
I'm using mysql and yes, the 2nd query is returning events where topic_id = 75. Just to note, I am using rails but this is a custom query. — b_d, Jun 26 '11 at 13:22

gbn · Accepted Answer · 2011-06-26T13:27:37.460

4

What this means is that you have other topic_id values in events_topic for a given event. A.k.a., if you have topic_id = 75 and topic_id = 33 for, say, eventid = 45, this eventid will be returned because the query matches on topic_id = 33

You have asked:

"give me all events where topic_id <> 75 does not exist"

Change the query to EXISTS/NOT EXISTS which is correct. This is:

"give me all events where topic_id = 75 does not exist"

SELECT `events`.* 
FROM `events`
WHERE
    EXISTS (SELECT * FROM events_topics.event_id
            WHERE events_topics.topic_id = 75
                     AND
                      events.id = events_topics.event_id
            )


SELECT `events`.* 
FROM `events`
WHERE
    NOT EXISTS (SELECT * FROM events_topics.event_id
            WHERE events_topics.topic_id = 75
                     AND
                      events.id = events_topics.event_id
            )

edited Jun 26 '11 at 13:27

answered Jun 26 '11 at 13:22

gbn

422,506
82
585
676

Thank you so much for the clarification. I knew my logic was lacking somewhere. Time to test it out. – b_d Jun 26 '11 at 13:27
question ... what's the difference between your response and x2's response? – b_d Jun 26 '11 at 13:35
@b_d: i think these 2 answers (and mine) give same results but may differ in performance only: See here: http://stackoverflow.com/questions/2246772/whats-the-difference-between-not-exists-vs-not-in-vs-left-join-where-is-null – ypercubeᵀᴹ Jun 26 '11 at 13:43
1

@b_d: NOT IN and NOT EXISTS work the same mostly but EXISTS is more correct generally (eg if you have a NULL in the event_id list from events_topics then NOT IN fails). – gbn Jun 26 '11 at 13:59

score 3 · Answer 2 · answered Jun 26 '11 at 13:22

3

Maybe something like this:

select * from Events
where id not in (select event_id from events_topics where topic_id=75)

answered Jun 26 '11 at 13:22

x2.

9,554
6
41
62

score 2 · Answer 3 · edited Jun 26 '11 at 13:59

2

Your attempt shows all events (topics are not shown) and just removes events-topics combinations with topic id=75. All other topics (with id<>75) are still there, so any event with a topic of 7 or 63 or whatever else will still be shown.

You only managed to not show events where their only topic has id 75.

But I think you want events that none of their topics has id=75:

SELECT events.* 
FROM events 
  LEFT JOIN events_topics
    ON events.id = events_topics.event_id
    AND events_topics.topic_id = 75
WHERE events_topics.event_id IS NULL

I think the above is equivalent with the NOT EXISTS and NOT IN versions you have as answers. Their difference is only in matters of performance.

edited Jun 26 '11 at 13:59

gbn

422,506
82
585
676

answered Jun 26 '11 at 13:22

ypercubeᵀᴹ

113,259
19
174
235

This will still give rows where topic_id = 33 as per my answer. – gbn Jun 26 '11 at 13:28
@gbn: I think not. My answer will show the same results with yours. I've included the `events_topics.topic_id = 75` condition in the `ON` clause of the `LEFT JOIN` so it's the same as the `NOT EXISTS` solution. – ypercubeᵀᴹ Jun 26 '11 at 13:32
You're right, please accept my apologies. I tend to use EXISTS mostly – gbn Jun 26 '11 at 13:59
@gbn: No problem. I agree that `LEFT JOIN / IS NULL` is the worst of the 3 regarding clarity. – ypercubeᵀᴹ Jun 26 '11 at 14:02

score 0 · Answer 4 · edited Jun 27 '11 at 12:24

0

Try '75' instead of 75 :

SELECT `events`.*  
FROM `events` 
INNER JOIN events_topics ON events.id =        events_topics.event_id  
WHERE (events_topics.topic_id <> '75')

edited Jun 27 '11 at 12:24

ypercubeᵀᴹ

113,259
19
174
235

answered Jun 26 '11 at 13:23

sara

3,824
9
43
71

1

How is this different to what the was asked? – gbn Jun 26 '11 at 13:24
The '', found it sometimes usefull. – sara Jun 26 '11 at 13:25
Ah, see it now. I didn't downvote but this won't be a solution: it's an EXISTS/IN type problem – gbn Jun 26 '11 at 13:26

score 0 · Answer 5 · answered Jun 26 '11 at 13:27

Your query not having any conflict but please make sure about data..And why you are having single quotes in besides event like this `events??

Check these

 select events.id from events where events.id <> 75

and

 select events_topics.event_id from events_topics where events_topics.event_id <> 75

If these are giving proper data then try this way

  SELECT events.* 
  FROM events
  INNER JOIN events_topics  ON events.id = events_topics.event_id and    
   events_topics.topic_id <> 75

sql problem with WHERE ('field' <> value)

5 Answers5