CODEWITHSUNDEEP
mysql
Jeffz
Jeffz

Reputation: 2105

MySQL unexpected result

I have this data:

id  message_id  recipient   user_id area    name
1   1754        0           95      1       Ruecker
2   1754        1           1       4       Heller
3   1754        2           2       1       Kuhn
4   1754        2           3       4       Schuster

I would like to get results where user_id!=1 and area!=4 (excluding Heller). So I want to get rows with ids: 1,3,4.

I tried this code:

select * from `message_participants` 
where `message_id` = 1754 and (`user_id` != 1 and `area` != 4)

I am getting only Ruecker and Kuhn. Schuster is also being excluded.

Can someone please explain why?

I have settled for this query (for now) - but it seems like an overkill: 

select * from `message_participants` 
where `message_id` = 1754 and 
`id` NOT IN (select id from `message_participants` 
             where `message_id` = 1754 and `user_id` = 1 and `area` = 4)

Upvotes: 1

Views: 53

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You could use not in() as

select * from 
`message_participants` 
where `message_id` = 1754 
and (`user_id` ,`area`) not in((1,4));

Demo

Or could use case expression 

select * from 
`message_participants` 
where `message_id` = 1754 
and case when user_id = 1 then area <> 4 else 1 end;

Demo

Upvotes: 2

t..
t..

Reputation: 1101

The reason Schuster is also being excluded because it has area = 4 which you are excluding by the line that states area != 4.

The ( ) isnt going to make the query sort by userid 1 and area 4 together, it will do that for all of the results. Which in turn sorts out Schuster because of the area value.

this is a possible solution for you by using or:

select * from `message_participants` 
where `message_id` = 1754 and `user_id` != 1 or `area` != 4

Upvotes: 1

Related Questions