CODEWITHSUNDEEP
mysqlsql
Alvin
Alvin

Reputation: 416

how to check a column with multiple conditions in sql?

I want to check a column for multiple conditions.. the columns are

user_id    auction_id    status
  7            102         L
  7            103         W
  7            104         B
  7            105        null

now i want to select a auction_id based on userid and status so this is the initial code that i wrote

select auction_id 
from user_auction_rel 
where user_id =7 
  and status = 'B' 
   OR STATUS = NULL ;

the result was i got the auctionid 104 as status B was given first. after checking out on the net, i came up with this code 

select auction_id 
from user_auction_rel 
where status in ('L' , 'W') 
group by user_id = 7;

but this also returned a similar result.. only auction id with status L was returned.. so i need an sql query that will be able to return auction id based on both the statuses.. thanks in advance..

Upvotes: 3

Views: 1707

Answers (4)

Michael Berkowski
Michael Berkowski

Reputation: 270609

There are two issues with your first attempt. First, you cannot compare NULL with the equality operator. You must use STATUS IS NULL instead.

Second, operator precedence is causing issues here. You need to enclose the status = 'B' OR STATUS IS NULL in a () group. Otherwise, the AND binds more tightly than the OR and you'll get incorrect results.

SELECT
  auction_id
FROM user_auction_rel
WHERE
  user_id =7
  AND (status = 'B' OR status IS NULL)
  /* or using the IN(): */
  /* AND (status = IN('L','W') OR status IS NULL) */

Here's a demonstration: http://sqlfiddle.com/#!2/eb36e0/1

Your second attempt goes awry because of the GROUP BY. Since you have no aggregates in the query (MAX(),MIN(),COUNT(),SUM()) there is no reason to use GROUP BY. The expression group by user_id = 7 actually evaluates as GROUP BY 1 and all results get grouped together. Where that would be an error in most other RDBMS, MySQL allows it and gives you an indeterminate result set back for the auction_id column.

Upvotes: 3

jvicab
jvicab

Reputation: 286

try

SELECT auction_id FROM user_auction_rel WHERE user_id =7 AND (status = 'B' OR status IS NULL);

You need to enclose OR parts in parenthesis due to AND takes precedence. Also to check for null value, use IS NULL instead of = NULL

Upvotes: 0

Aman Aggarwal
Aman Aggarwal

Reputation: 18439

The query should be:

select auction_id from user_auction_rel where user_id =7 and status = 'B' OR STATUS IS NULL ;

From this you will get both rows having status is null and status = B

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20794

Parentheses are your friends. Your first query was this:

select auction_id 
from user_auction_rel  
where user_id =7  
and status = 'B' 
OR STATUS = NULL

But adding brackets changes the meaning altogether

select auction_id 
from user_auction_rel  
where user_id =7  
and 
(
status = 'B' 
OR STATUS is NULL 
)

Upvotes: 2

Related Questions