SQLTeam.com | Weblogs | Forums

SQL server 2014 - select from table based on multiple conditions in where clause

tsql
sql2014

#1

i've the below table.

i want to select all userid's where rank=1 and program='tennis' AND rank=2 and program='cricket' AND rank=3 and program='football'. As per the table data the result should be user2 and user3.

i tried the below but i doesn't seem to work. Can someone please help out.

select userid from table
where (rank=1 and program='tennis') and (rank=2 and program='cricket') and (rank=3 and program='football')


#2

Use OR instead of AND between the conditions.

where (rank=1 and program='tennis') OR (...


#3

There is no row in your table that satisfies. since a row can have only one rank and one program. try this instead:

...where (rank = 1 and program = 'tennis') or (rank = 2 and program = 'cricket') or (rank = 3 and program = 'football')


#4

Hi, it doesn't seem to be working. i'm getting back all the users in the result. i want to get only user2 and user3 in the result since only for these 2 users it satisfies the condition which i'm looking for (ie, the rank=1 and program='tennis', rank=2 and program='cricket' and rank=3 and program='football', all 3 conditions satisfied together). I think with the OR in between it returns userid's satisfying each condition type individually.

For user1 if you see the data says, rank=1 and program='tennis', rank=2 and program='football' and rank=3 and program='cricket' which does not satisfy my condition.


#5

Hi, With the OR in between it doesn't seem to work. i'm getting back all the users in the result. i want to get only user2 and user3 in the result since only for these 2 users it satisfies the condition which i'm looking for (ie, the rank=1 and program='tennis', rank=2 and program='cricket' and rank=3 and program='football', all 3 conditions satisfied together). I think with the OR in between it returns userid's satisfying each condition type individually.

For user1 if you see the data says, rank=1 and program='tennis', rank=2 and program='football' and rank=3 and program='cricket' which does not satisfy my condition.


#6

Try

select userid 
from table
where 
    case when rank=1 and program='tennis' then 1 else 0 end + 
    case when rank=2 and program='cricket' then 1 else 0 end + 
    case when rank=3 and program='football' then 1 else 0 end = 3

#7

@djj55

Hi, I tried the above query as is but i'm getting no results.


#8
SELECT userid
FROM table_name
--just for efficiency, to ignore rows that don't match what we're checking for.
WHERE rank IN (1,2,3) AND program IN ('cricket','football','tennis')
GROUP BY userid
HAVING COUNT(*) = 3
/* if you ever need more specific checks, you can do something like this:
HAVING MAX(CASE WHEN rank=1 AND program='tennis' THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN rank=2 AND program='cricket' THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN rank=3 AND program='football' THEN 1 ELSE 0 END) = 1
*/
ORDER BY userid

#9

I wish you had said that at the begging!


#10

@ScottPletcher

Hi, The query seems to be working. Thanks.


#11

@gbritton

Hi, sorry, I should have specified earlier that all 3 conditions should be satisfied.


#12

@ScottPletcher

Hi, could you please explain the working of the below. i'm a little new to using the case statement in the having clause

HAVING MAX(CASE WHEN rank=1 AND program='tennis' THEN 1 ELSE 0 END) = 1 AND
MAX(CASE WHEN rank=2 AND program='cricket' THEN 1 ELSE 0 END) = 1 AND
MAX(CASE WHEN rank=3 AND program='football' THEN 1 ELSE 0 END) = 1

Thanks


#13

Sure.
"HAVING MAX(CASE WHEN rank=1 AND program='tennis' THEN 1 ELSE 0 END) = 1"
This will check every row for " rank=1 AND program='tennis' ". If it matches, the CASE will return 1, if not, it will return 0. Since we're using MAX(), if 1 ever gets returned, the final result will be 1, since 1 will always be max over 0. If no match is ever found, the final result will be 0. Therefore, we can check the result for 1 to see if the condition was ever met.

The result is for whatever column(s) we're GROUPing BY. That is the difference between WHERE and HAVING: WHERE looks at only the current row, but HAVING considers all rows the the GROUP.


#14

@ScottPletcher

Thanks a lot for the explanation