Click here to Skip to main content
15,881,803 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm tying to do a NFL sport pick all pool. Users pick a winner from every game. I'm trying to write a Stored Procedure that will show the results of the users pick and if there pick is correct or wrong. I'm using a CASE statement but it obviously is not working. When I display this procedure on my site only the first game results are correct.

I'm not sure if this is the best way to go about this or if I should use a different query other than CASE to get what I'm looking for.

My UserPicks table collects all the picks from the user. My WonLost table has the name of the team that won the game(GameResults_1, etc...).

Thanks in advance for any help.

What I have tried:

SQL
SELECT UserPicks.FullName, UserPicks.Game_1, UserPicks.Game_2, UserPicks.Game_3, UserPicks.Game_4, UserPicks.Game_5,
	   WonLost.GameResults_1, WonLost.GameResults_2, WonLost.GameResults_3, WonLost.GameResults_4, WonLost.GameResults_5,
(
CASE 
WHEN UserPicks.Game_1 = WonLost.GameResults_1
	THEN  'Won' 

WHEN UserPicks.Game_2 = WonLost.GameResults_2
	THEN 'Won' 

WHEN UserPicks.Game_3 = WonLost.GameResults_3
	THEN 'Won' 

WHEN UserPicks.Game_4 = WonLost.GameResults_4
	THEN 'Won' 

WHEN UserPicks.Game_5 = WonLost.GameResults_5
	THEN 'Won' 

	ELSE 'Lost'
	
END 
) AS Results
FROM UserPicks

JOIN WonLost ON UserPicks.Week = WonLost.Week

WHERE WonLost.Week = 'Week1'
Posted
Updated 25-Apr-18 22:40pm
v2
Comments
Santosh kumar Pithani 25-Apr-18 23:37pm    
Your case statement is not clear i mean more than one case condition is true.You need to write ex:"CASE
WHEN (
(UserPicks.Game_1 = WonLost.GameResults_1) OR
(UserPicks.Game_2 = WonLost.GameResults_2)
)
THEN 'WON' ELSE LOST' END"
Commish13 26-Apr-18 12:49pm    
This statement produces the same results. When the first game is correct it also displays the rest of the games are correct even though some of the other games are incorrect. Same thing if the first game is incorrect then it displays the rest of the games incorrect

To me the it looks like there's a flaw in the design. At the moment you store the user picks and the weekly results in a single table containing 5 column each row. What happens for example if
- There are 6 matches a week
- User picks more/less
- The match numbers do not correlate, for example user pick 3 is actually wonlost 2
- And so on.

In my opinion you should store both picks and matches as rows. Consider the following example:
User -table
----------
userid
name
...

Game -table
----------
GameId
Week

UserPick -table
------------
Userid
GameId

GameResult -table
-------------
GameId
WinnerTeamId
and so on...

With a structure having this kind of approach ot would be easier to maintain the situation and to accomplish desired queries.

For more information, have a look at Third normal form - Wikipedia[^]
 
Share this answer
 
Comments
Maciej Los 26-Apr-18 1:54am    
5ed!
Commish13 26-Apr-18 12:54pm    
I have 2 different tables that accept users picks for each game and a table that holds the winners of the games. Each users picks and winners of each game is in a row format. I'm having trouble displaying a Win or Lost next to the users picks depending on the result of the game.
Wendelius 26-Apr-18 12:57pm    
I'm sorry but I'm having a hard time understanding your description. Perhaps some example data?
In addition to solution #1 by Wendelius[^], i'd change your database design as follow (tables):
1. User (stores information about users)
2. Game (stores information about games, like: poker, bridge, etc.)
3. Tour (stores information about games started at specific time /such of session/)
4. Tour_Details (stores information about users playing game and their results)

SQL
--Note: declaration of tables is incomplete, due to training purposes...

DECLARE @user TABLE(UserId INT IDENTITY(1,1), UserName VARCHAR(50))
INSERT INTO @user(UserName)
VALUES('John'), ('Gina'), ('Frank'), ('Dolores'), ('Sebastian'), ('Angel'), ('Robert'), ('Eve')

DECLARE @game TABLE(GameId INT IDENTITY(1,1), GameName VARCHAR(50))
INSERT INTO @game(GameName)
VALUES('Poker'), ('Bridge')

DECLARE @tour TABLE(TourId INT IDENTITY(1,1), StartedAt DATETIME, GameId INT)
INSERT INTO @tour(StartedAt, GameId )
VALUES('2018-04-25 18:05:37.000', 1), ('2018-04-25 21:15:12.000', 2)

DECLARE @tour_details TABLE(TourDetailId INT IDENTITY(1,1), TourId INT, UserId INT, ReachedPoints INT)
INSERT INTO @tour_details(TourId, UserId, ReachedPoints)
VALUES(1, 1, 0), (1, 2, 2500), (1, 3, 750), (1, 4, 500),
(2, 7, 2500), (2, 8, -500), (2, 5, 2500), (2, 6, -500)  


SELECT TR.TourId, G.GameName, T.StartedAt, U.UserName, TR.ReachedPoints
FROM @tour_details AS TR 
	INNER JOIN @user AS U ON TR.UserId = U.UserId 
	INNER JOIN @tour AS T ON TR.TourId = T.TourId 
	INNER JOIN @game AS G ON G.GameId = T.GameId 
ORDER BY TR.TourId ASC, TR.ReachedPoints DESC


Sample output:
TourId	GameName	StartedAt	UserName	ReachedPoints
1	Poker	2018-04-25 18:05:37.000	Gina	2500
1	Poker	2018-04-25 18:05:37.000	Frank	750
1	Poker	2018-04-25 18:05:37.000	Dolores	500
1	Poker	2018-04-25 18:05:37.000	John	0
2	Bridge	2018-04-25 21:15:12.000	Sebastian	2500
2	Bridge	2018-04-25 21:15:12.000	Robert	2500
2	Bridge	2018-04-25 21:15:12.000	Eve	-500
2	Bridge	2018-04-25 21:15:12.000	Angel	-500


As you can see, whenever users start playing new game, new tour is opened, then TourID within every single UserID is stored in Tour_Details table. Such of design enable you to store details about single- and multi-users games.

Good luck!
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900