Script Help .net

Question

Hi Guys,

I have been creating a website that is going to store predictions for UK Football results. I have been able to save all of the data into a database and everything is working correctly. Now its time for the hard part and thats why i need some help.

I currently have a table holding  The FIXTURES, PREDICTIONS and USERS.

I have added a table called RESULTS (all of the table information will be available at the bottom of this post.

What i would like to do is at the end of every week i would like to run a Script that will check the predictions that the users have made and if they are the same as the Fixture results then i would like to add a 1 into the RESULTS table.

For example:

From the FIXTURE table i have MATCH1HR (Match 1 Home Result) and MATCH1AR (Match 1 Away Result). Lets say that in these columns they both have 1 (So the score of the match was [TeamA 1 vs 1 TeamB])

From the PREDICTIONS table the user has predicted that the results of MATCH1 would be 1 - 1 these results are stored within the PREDICTIONS table under the Columns PRE1H (Prediction Match1 Home Result) and PRE1A (Prediction Match1 Away Result) so the value in both of these columns would be 1.

What i would like to do is if PRE1H matches the same value as MATCH1HR and PRE1A matches the same value as MATCH1AR then i would like to populate the RESULTS table under the column RESULT1H with the value of 1.

I am unsure of how to do this my only guess was to create a Insert using WHERE statements but i am still unsure of how to achieve this.

There is more than 1 match within my database i am just using one for this example to test it so i know how to go about creating the SQL.

Predictions are unique by using the date, when i create a fixture list the date i added the information is inserted within the FIXTURES table and when the user goes to add a prediction they get a choice of what date to make a prediction against. Therefore when the script is ran it would need to insert records into the RESULTS table for every user that has made a prediction for the most current date.

I am creating this within .NET using C# and using a MS SQL Server.

Below are my tables within the database, if you would like anymore information please just ask.

P.S. I know this is a little confuising but if you have any questions please feel free.

FIXTURES

FIXTURE_ID
FIXTURE_DATE
MATCH1HR
MATCH1AR
MATCH2HR
MATCH2AR
MATCH3HR
MATCH3AR
MATCH4HR
MATCH4AR
MATCH1HID
MATCH1AID
MATCH2HID
MATCH2AID
MATCH3HID
MATCH3AID
MATCH4HID
MATCH4AID

USERS

USER_ID
FIRSTNAME
SURNAME
USERNAME
PASSWORD
DOB

PREDICTIONS

PREDICTION_ID
USER_ID
PRE1H
PRE1A
PRE2H
PRE2A
PRE3H
PRE3A
PRE4H
PRE4A
DATE_OF_PREDICTION

RESULTS

RESULT_ID
RESULT_DATE
RESULT1
RESULT2
RESULT3
RESULT4

Thanks Boldonglen