trouble with IN clause in sql

Question

SELECT bm.WinningNumber,bd.BetOnNumber,"WinLossAmount" = 
      CASE            
         WHEN 2 in (2,1) THEN ('WIN')
               END    
FROM BettingMaster bm inner join BettingDetail bd on bm.GameID = bd.GameID
where bd.GameID = 1

This works as a Charm and I get 'WIN' in WinLossAmount. Now I actually have value 2 in column WinningNumber(varchar) and value 2,1 in column BetOnNumber(varchar). I retried the statement as

SELECT bm.WinningNumber,bd.BetOnNumber,"WinLossAmount" = 
      CASE            
         WHEN bm.WinningNumber in (bd.BetOnNumber) THEN ('WIN')
               END    
FROM BettingMaster bm inner join BettingDetail bd on bm.GameID = bd.GameID
where bd.GameID = 1

This doesn't work. Is it not possible this way?! Any help??

bm.WinningNumber in (bd.BetOnNumber) is equivalent (example) with 2 in ('2,1') which is always false — wxyz, Jan 09 '14 at 12:05
A slight side note - if you find yourself putting comma separated values into a column in a database table, your design is probably wrong. These should probably be in a separate BetOnNumbers table, linked to your BettingMaster table ID (this would make this query simpler, and probably save you time in the longer term). — Paddy, Jan 09 '14 at 12:08
Thanks Paddy, yeah really makes sense and I agree but i've a scenario where this needs to be done — user3126947, Jan 09 '14 at 12:12
What database are you using? If it's MySQL, you can use `FIND_IN_SET` to match against a comma-separated list. — Barmar, Jan 09 '14 at 12:19

score 0 · Answer 1 · edited May 23 '17 at 12:21

I believe the problem is that in the first statement, SQL is comparing 2 to 1 and then 2 to 2, all integers. Obviously, finding the match and working.

In the second case, you're comparing 2 to a string of "1,2" and 2 does not equal "1,2". You'll need to split the varchar into a series of integers before you compare it. Take a look at:

Parse comma-separated string to make IN List of strings in the Where clause

This should help you out.

score 0 · Answer 2 · answered Jan 09 '14 at 12:12

You can't just use IN and then specify a column name, you need to use a subquery:

SELECT  bm.WinningNumber ,
        bd.BetOnNumber ,
        "WinLossAmount" = CASE WHEN bm.WinningNumber IN (
                                    SELECT  bd.BetOnNumber
                                    FROM    BettingMaster bm
                                            INNER JOIN BettingDetail bd ON bm.GameID = bd.GameID
                                    WHERE   bd.GameID = 1 ) THEN ( 'WIN' )
                          END
FROM    BettingMaster bm
        INNER JOIN BettingDetail bd ON bm.GameID = bd.GameID
WHERE   bd.GameID = 1

score 0 · Accepted Answer · answered Jan 09 '14 at 12:26

IN operator in SQL searches values in the row set. But in the second example you try to find INT value in the STRING so you can't use IN this way.

In the most SQL systems you can try to use following condition. Where + is a concatenate operator in MSSQL (in Oracle ||, in MySQL CONCAT())

WHEN ','+bd.BetOnNumber+',' LIKE '%,'+CAST(bm.WinningNumber AS VARCHAR(10))+',%' 
     THEN ...

Also in MySQL you can use FIND_IN_SET() function:

WHEN FIND_IN_SET(bm.WinningNumber,bd.BetOnNumber) THEN ...

PS: Both ways can't use indexes so you shouldn't use them on big tables.

Thanks Valex, it worked..Just copy pasted it thinking why am i doing this :) ..Guess have to look through my whole design again. But thanks a ton again!! — user3126947, Jan 10 '14 at 06:41

trouble with IN clause in sql

3 Answers3