SQL Query Again - Complex
-
Need some help again, this is going to get abit complex.
I have a Table call Bets. This tables stores bets(numbers) placed by users.
Here are the columns in the Bet Table.
BET_ID
BET_DATE
DRAW_ID
BET_TYPE
BET_NUMBER1
BET_NUMBER2
BET_NUMBER3
BET_NUMBER4
BET_NUMBER5
So now, I need to Query my Table for matching Numbers. So lets say my Winning Numbers are 2,4,6,8,9. I need to query by Table for these numbers. But the problem is BET_NUMBER1 to 5 have different numbers.
Lets say my BET_NUMBER1 = 1, BET_NUMBER2 = 2, BET_NUMBER3 = 8, BET_NUMBER4 = 20, BET_NUMBER5 = 45.
So how am I going to query.???
NEXT lets say Once I find a Matching Record, I wish to copy this Record onto another Table.
How do I go about doing that?
Thanks in advance.
-
-- declare 5 variables, one for each winning number
declare @win1 int, @win2 int, @win3 int, @win4 int, @win5 int
set @win1 = 2 ... and each of the others
Then,
select * from BetTable
where BET_NUMBER1 in (@win1, @wind2, @win3....)
and BET_NUMBER2 in (@win1, @wind2, @win3....)
... Do you get the picture?
-
I will give it a try.
Then how about copy that Matching record into another Table?
-
simple.
create a table with a structure identical to your bets table.
then
insert MyNewTable
select ... -- copy that entire query
-
I forgot to Mention. That If I use all that condition, that means I need to get 5 matching numbers.
But what if i have 3 matching numbers?
How can i have the query now?
-
Could you show me in SQL. Thanks
-
Insert into AnotherTable(BET_ID,BET_DATE,DRAW_ID,BET_TYPE,BET_NUMBER1,BET_NUMBER2,BET_NUMBER3,BET_NUMBER4,BET_NUMBER5)
select BET_ID,BET_DATE,DRAW_ID,BET_TYPE,BET_NUMBER1,BET_NUMBER2,BET_NUMBER3,BET_NUMBER4,BET_NUMBER5
from BetTable
where BET_NUMBER1 in (@win1, @wind2, @win3, @win4, @win5)
and BET_NUMBER2 in (@win1, @wind2, @win3, @win4, @win5)
and BET_NUMBER3 in (@win1, @wind2, @win3, @win4, @win5)
and BET_NUMBER4 in (@win1, @wind2, @win3, @win4, @win5)
and BET_NUMBER5 in (@win1, @wind2, @win3, @win4, @win5)
-
I must recommend you to change your schema. I would definitely move the betnumbers to a separate table, since that would make the querying much easier. For instance:
CREATE TABLE BETNUMBERS (BET_ID INT NOT NULL FOREIGN KEY REFERENCES BETS (BET_ID)
, BET_NUMBER INT NOT NULL
, PRIMARY KEY (BET_ID, BET_NUMBER)
)
If you want to get all bets having at least 3 numbers correct you could use the following simple query:
SELECT n.BET_ID, COUNT(*) AS NUMBERS_CORRECTFROM BETNUMBERS n
INNER JOIN BETS b ON n.BET_ID = b.BET_ID
WHERE b.DRAW_ID = @draw_id
AND n.BET_NUMBER IN (
@winning_number_1
, @winning_number_2
, @winning_number_3
, @winning_number_4
, @winning_number_5)
GROUP BY n.BET_ID
HAVING COUNT(*) >= 3
-
Ok, I have some changes...I do not quite get what u are saying for the change in table.
Table has BetID, Bet_Numbers 1 to 8.
Now I want to query my Table with 5 Winning Numbers.
I need to get Bets that have 3,4,5 matching numbers.
So how do i do that.
-
Like I said, I would not use the table design that you have. I would move the numbers to a separate table. The only problem with doing that is that it is difficult to specify the constraint that there should be exactly 8 numbers per bet. Also, if the order of the numbers have any significance (i.e. it is important to know if it was bet_number_1 that was correct or bet_number_2) then this might not be the best solution. But I am assuming that these issues are not a problem. If so, this solution is easy and performs well for handling your question:
CREATE TABLE BETS (BET_ID INT NOT NULL PRIMARY KEY
-- other columns removed for brevity
, DRAW_ID INT NOT NULL
)
INSERT INTO BETS (BET_ID, DRAW_ID) VALUES (1, 10)INSERT INTO BETS (BET_ID, DRAW_ID) VALUES (2, 10)
INSERT INTO BETS (BET_ID, DRAW_ID) VALUES (3, 10)
CREATE TABLE BETNUMBERS (BET_ID INT NOT NULL FOREIGN KEY REFERENCES BETS (BET_ID)
, BET_NUMBER INT NOT NULL
, PRIMARY KEY (BET_ID, BET_NUMBER)
)
INSERT INTO BETNUMBERS (BET_ID, BET_NUMBER) VALUES (1, 2)INSERT INTO BETNUMBERS (BET_ID, BET_NUMBER) VALUES (1, 3)
INSERT INTO BETNUMBERS (BET_ID, BET_NUMBER) VALUES (1, 5)
INSERT INTO BETNUMBERS (BET_ID, BET_NUMBER) VALUES (1, 7)
INSERT INTO BETNUMBERS (BET_ID, BET_NUMBER) VALUES (1, 8)
INSERT INTO BETNUMBERS (BET_ID, BET_NUMBER) VALUES (1, 10)
INSERT INTO BETNUMBERS (BET_ID, BET_NUMBER) VALUES (1, 12)
INSERT INTO BETNUMBERS (BET_ID, BET_NUMBER) VALUES (1, 13)
INSERT INTO BETNUMBERS (BET_ID, BET_NUMBER) VALUES (2, 1)
INSERT INTO BETNUMBERS (BET_ID, BET_NUMBER) VALUES (2, 3)
INSERT INTO BETNUMBERS (BET_ID, BET_NUMBER) VALUES (2, 4)
INSERT INTO BETNUMBERS (BET_ID, BET_NUMBER) VALUES (2, 6)
INSERT INTO BETNUMBERS (BET_ID, BET_NUMBER) VALUES (2, 7)
INSERT INTO BETNUMBERS (BET_ID, BET_NUMBER) VALUES (2, 9)
INSERT INTO BETNUMBERS (BET_ID, BET_NUMBER) VALUES (2, 11)
INSERT INTO BETNUMBERS (BET_ID, BET_NUMBER) VALUES (2, 13)
INSERT INTO BETNUMBERS (BET_ID, BET_NUMBER) VALUES (3, 1)
INSERT INTO BETNUMBERS (BET_ID, BET_NUMBER) VALUES (3, 2)
INSERT INTO BETNUMBERS (BET_ID, BET_NUMBER) VALUES (3, 5)
INSERT INTO BETNUMBERS (BET_ID, BET_NUMBER) VALUES (3, 6)
INSERT INTO BETNUMBERS (BET_ID, BET_NUMBER) VALUES (3, 7)
INSERT INTO BETNUMBERS (BET_ID, BET_NUMBER) VALUES (3, 8)
INSERT INTO BETNUMBERS (BET_ID, BET_NUMBER) VALUES (3, 11)
INSERT INTO BETNUMBERS (BET_ID, BET_NUMBER) VALUES (3, 12)
DECLARE @draw_id INTSET @draw_id = 10
DECLARE @winning_number_1 INT, @winning_number_2 INT, @winning_number_3 INT, @winning_number_4 INT, @winning_number_5 INTSET @winning_number_1 = 1
SET @winning_number_2 = 6
SET @winning_number_3 = 8
SET @winning_number_4 = 9
SET @winning_number_5 = 12
SELECT n.BET_ID, COUNT(*) AS NUMBERS_CORRECTFROM BETNUMBERS n
INNER JOIN BETS b ON n.BET_ID = b.BET_ID
WHERE b.DRAW_ID = @draw_id
AND n.BET_NUMBER IN (
@winning_number_1
, @winning_number_2
, @winning_number_3
, @winning_number_4
, @winning_number_5)
GROUP BY n.BET_ID
HAVING COUNT(*) >= 3
-
I also need to calcuate the number of BetIDs which has the winning numbers? How do i do that??
Btw, for my Bet Numbers I can actually have some numbers Null. Lets say A user can bet minimum 5 numbers and a maximum of 8 numbers. So how do I manage this? Leave my Column for Bet_numbers null?
-
Im actually programming in asp.net. So if my user bets on 8 numbers, i actually need to do 8 Insert Statements??
Is this right? Instead of a Single Insert Statement where I can insert my values inside a single row?
Please advise? thanks
-
I'll give you the logic to follow, but I'm too busy now to write out all of the code. Check BOL if you need help in creating tables.
Create a table of bets.
fields: unique identifier
bettor name
bet date
bet#1
bet#2
bet#3
bet#4
bet#5
bet#6
bet#7
bet#8
score -- number of correct matches
populate your table with the data. enter 0 for score
compare each of the 8 bet numbers to the winning numbers, and get a score between 0 and 8 for each bettor. Then you can pull out who matched 8 numbers, 7 numbers, etc.
update TableOfBets
set score = score + 1
where bet#1 in (@win1, @win2, @win3......)
update TableOfBets
set score = score + 1
where bet#2 in (@win1, @win2, @win3......)
you can write 8 update statements, or use a loop statement and dynamic sql to build the query, but, if you are still in the stage of using BOL to create a table, use the long way!)
Best of luck,
Sara.
PS. Please let me know if this helps.
-
Your idea seems good. I have tried, and only the first row of my table is being updated. ??
-
instead of update, run the statement as select.
Are you only getting one records that matches any of the numbers?
post all of the code, and I (or someone faster on the draw) will review it for you.
Viewing 15 posts - 1 through 15 (of 78 total)
You must be logged in to reply to this topic. Login to reply