CODEWITHSUNDEEP
sqlsql-servert-sql
bbl
bbl

Reputation: 11

Trying to exclude certain users

New to SQL but I'm trying to find titles wherein the words "blue" and "black" are present. However, they cannot be from users who have ids of 1 and 5. This is what I did:

SELECT title, id_card
FROM site
WHERE (title LIKE "%blue%" OR title LIKE "%black%")
  AND (id_card != 1 OR id_card != 5)
ORDER BY id_card ASC;

However, when I add that id 1 and id 5 shouldn't be in the query, it shows me something like this:

Blog Title User
The blue sky was so nice today 1
Black is my favorite color 2
I'm feeling blue 2
She was dressed in black 3

However, when I erase id_card != 5 from the code, it works perfectly fine.

Blog Title User
Black is my favorite color 2
I'm feeling blue 2
She was dressed in black 3

I'm just wondering where I went wrong and how I can fix this issue. Thank you in advance!

Upvotes: 0

Views: 263

Answers (2)

allmhuran
allmhuran

Reputation: 4454

Gordon has given you the literal answer but I'm not sure whether it helped you understand the original mistake, so I'm going to add a little more information.

Your original construct was...

AND (id_card != 1 OR id_card != 5)

Imagine the id card of a row was 1. Let's check the first condition

id_card != 1

Does our row pass this test? No, because the id card does equal 1. So we don't pass this test. The expression 1 != 1 is false.

But wait, we have an or condition here. So if the or condition is satisfied, the whole condition might still be true. Let's check it...

id_card !=5

Does 1 not equal 5? Yes, 1 does not equal 5. So we do pass this test. The expression 1 !=5 is true.

So if we look at the expression as a whole, we get:

and (false or true)

Well, since one side of the or condition is true, the whole condition is true. That's how or works.

Since the condition is true, this part of the where clause isn't going to filter it out. So we get back rows where id_card = 1.

Notice that the condition (id_card != 1 or id_card != 5) will be true no matter what value the id_card has, because for any value you choose, the value can't be both 1 and 5. Either it's not 1, or it's not 5. So at least one side of the or condition is always true.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269853

You are on the right track. But use not in to get the logic right:

SELECT title, id_card
FROM site
WHERE (title LIKE '%blue%' OR title LIKE '%black%') AND
      id_card NOT IN (1, 5)
ORDER BY id ASC;

You could also fix your logic by using AND.

Upvotes: 6

Related Questions