I am building a Hockey Sports score and prediction system using PHP/MySQL. Below are the system design.

I have a GAMES table where two team numbers and their score in the game is present.The columns from this table are as below.

```
ID ---- TEAM1 ---- SCORE1 ---- TEAM2 ---- SCORE2
1       70         1           73         2
2       74         0           70         1
3       74         0           73         0

```

I also have a PICKS table where the details related to user's game predictions are present. Users can guess which team will win in a game and that data is stored in this table. The columns from this table are as below. Each user can guess only once for each game.

```
ID ---- GAME ---- USER ---- TEAM ---- POINT
1       1         1         70        1
2       2         1         70        1
3       3         1         73        1
3       1         2         70        1

```

Based on the above available data, I am trying to build up the result where each user (column USER) should be awarded the points(column POINT) for each correct guess. The guess can be validated based on the scores from GAMES table. The final output should be like as below.

```
USER ---- POINTS ---- CORRECT GUESS COUNT ---- WRONG GUESS COUNT
1         1           1                        2
2         0           0                        1

```

The columns "CORRECT GUESS COUNT" and "WRONG GUESS COUNT" represent the total number of correct guess and wrong guess done by the user.

I have created a SQL Fiddle for the above tables with some sample data.

<http://sqlfiddle.com/#!2/8d469/4/0>

**EDIT:**

* Some more inforamtion are below. It's possible that a game can be a
draw.
* In that case the score will be 0 for each team. When a game is
draw, users get no points.