SQL Query Again - Complex

Question

Post reply

SQL Query Again - Complex

Viewing 15 posts - 16 through 30 (of 78 total)

You must be logged in to reply to this topic. Login to reply

raj-248044 SSC Eights! Points: 838 More actions · Answer 1

I am getting All records that matches any of the numbers.

raj-248044 SSC Eights! Points: 838 More actions · Answer 2

UPDATE TBETS

SET Score = Score + 1

WHERE BET_1 in ('1', '2','25','32','44')

UPDATE TBETS

SET Score = Score + 1

WHERE BET_2 in ('1', '2','25','32','44')

UPDATE TBETS

SET Score = Score + 1

WHERE BET_3 in ('1', '2','25','32','44')

UPDATE TBETS

SET Score = Score + 1

WHERE BET_4 in ('1', '2','25','32','44')

UPDATE TBETS

SET Score = Score + 1

WHERE BET_5 in ('1', '2','25','32','44')

UPDATE TBETS

SET Score = Score + 1

WHERE BET_6 in ('1', '2','25','32','44')

UPDATE TBETS

SET Score = Score + 1

WHERE BET_7 in ('1', '2','25','32','44')

UPDATE TBETS

SET Score = Score + 1

WHERE BET_8 in ('1', '2','25','32','44')

UPDATE TBETS

SET Score = Score + 1

WHERE BET_9 in ('1', '2','25','32','44')

UPDATE TBETS

SET Score = Score + 1

WHERE BET_10 in ('1', '2','25','32','44')

UPDATE TBETS

SET Score = Score + 1

WHERE BET_11 in ('1', '2','25','32','44')

UPDATE TBETS

SET Score = Score + 1

WHERE BET_12 in ('1', '2','25','32','44')

sara karasik SSCommitted Points: 1573 More actions · Answer 3

Your code seems ok.

Is BET_1 a datatype varchar, not int? I see that your values are in quotes.

can you run this

select * from TBETS

WHERE BET_12 in ('1', '2','25','32','44')

do you still get only one row returned?

after you run the code, what are the scores?

raj-248044 SSC Eights! Points: 838 More actions · Answer 4

select * from TBETS

WHERE BET_12 in ('1', '2','25','32','44')

I get 1 row, with all my columns empty.

sara karasik SSCommitted Points: 1573 More actions · Answer 5

Is your table populated?

Can you run

select *

from TBETS

and post it here?

Nicolas DONADIO MANOLIDIS Ten Centuries Points: 1118 More actions · Answer 6

Hi,

I'm not sure if tis could work, 'cause I'm at home and have no SQL Server to try it, but I hope you could catch the idea at least...

Supposing you don't have a MatchingBets column in your Bets table, I would create a #TempBets with BetId and MatchingBets columns... and we have WinnerNumbers in @Winner_1, @Winner_2, etc.

SELECT BetID, 0 as MatchingBets

INTO #TempBets

FROM Bets

UPDATE #TempBets

SET MatchingBets = ( ( CASE WHEN Bet_1 IN ( @Winner_1, @Winner_2...)

THEN 1 ELSE 0 )

+

( CASE WHEN Bet_5 IN ( @Winner_1, @Winner_2...)

THEN 1 ELSE 0 )

+

...

+

( CASE WHEN Bet_8 IN ( @Winner_1, @Winner_2...)

THEN 1 ELSE 0 )

)

FROM Bets

WHERE #TempBets.BetId = Bets.BetID

Good Luck!

Nicolas Donadio

raj-248044 SSC Eights! Points: 838 More actions · Answer 7

Im getting an error here

Line 14: Incorrect syntax near ')'. on this line

THEN 1 ELSE 0 )

sushila SSC-Dedicated Points: 35293 More actions · Answer 8

raj - put an "END" after the (THEN 1 ELSE 0) and run it again!

btw - is there no way you can change the schema of your table (as Chris suggested ?!)

**ASCII stupid question, get a stupid ANSI !!!**

raj-248044 SSC Eights! Points: 838 More actions · Answer 9

There is already an object named '#TempBets' in the database.

Im getting another error??

sushila SSC-Dedicated Points: 35293 More actions · Answer 10

ok...then first run:

"drop table #TempBets "

then run the rest of the statements...

**ASCII stupid question, get a stupid ANSI !!!**

raj-248044 SSC Eights! Points: 838 More actions · Answer 11

DECLARE @Winner_1 INT, @Winner_2 INT, @Winner_3 INT, @Winner_4 INT, @Winner_5 INT

SET @Winner_1 = 1

SET @Winner_2 = 2

SET @Winner_3 = 3

SET @Winner_4 = 4

SET @Winner_5 = 5

SELECT Bet_ID, 0 as MatchingBets

INTO #TempBets

FROM BETS

UPDATE #TempBets

SET MatchingBets = ( CASE WHEN BET_NUMBER_1 IN (@Winner_1, @Winner_2,@Winner_3,@Winner_4,@Winner_5)

THEN 1 ELSE 0 END )

+

( CASE WHEN BET_NUMBER_2 IN (@Winner_1, @Winner_2,@Winner_3,@Winner_4,@Winner_5)

THEN 1 ELSE 0 END )

+

( CASE WHEN BET_NUMBER_3 IN (@Winner_1, @Winner_2,@Winner_3,@Winner_4,@Winner_5)

THEN 1 ELSE 0 END)

+

( CASE WHEN BET_NUMBER_4 IN (@Winner_1, @Winner_2,@Winner_3,@Winner_4,@Winner_5)

THEN 1 ELSE 0 END)

+

( CASE WHEN BET_NUMBER_5 IN (@Winner_1, @Winner_2,@Winner_3,@Winner_4,@Winner_5)

THEN 1 ELSE 0 END)

FROM BETS

WHERE #TempBets.BET_ID = BETS.BET_ID

These are my query. I have added some rows(with winning numbers) in my BETS table..When I execute this sql query, and check my TempBets table, There arent any values inside my TempTable.

Please help.

sushila SSC-Dedicated Points: 35293 More actions · Answer 12

raj - do a "select * from BETS" & post the results here as sara suggested...maybe if we can look at the data, we might be able to help!

**ASCII stupid question, get a stupid ANSI !!!**

raj-248044 SSC Eights! Points: 838 More actions · Answer 13

ITs in table format..So let me enter the records.

BETID DRAWID USERID NUM1 NUM2 NUM3 NUM4 NUM5 NUM6 NUM7 NUM8

1 1 1001 1 2 4 7 12 19 21 26

2 1 1001 4 2 6 7 17 29 33 45

3 1 1001 1 2 4 7 10 19 23 35

4 1 1001 5 7 21 22 32 37 40 43

Nicolas DONADIO MANOLIDIS Ten Centuries Points: 1118 More actions · Answer 14

RAJ

Please, in Query Analyzer, press CTRL-T and next run a SELECT * FROM BETS

Post the results...

Thank you!

BTW... I'm sorry I forgot the ENDs... my wife was waiting for dinner!

Nicolas Donadio

sushila SSC-Dedicated Points: 35293 More actions · Answer 15

i'm sorry if i seem to be asking foolish questions but raj - are your column names 'num1', 'num2' etc... or did you just display that for brevity ?!

in other words, you ARE using the actual column names in your update statement right ?!

**ASCII stupid question, get a stupid ANSI !!!**