Sql CASE statement not working

Question

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 25-Apr-18 10:59am

Commish13

Updated 25-Apr-18 22:40pm

Maciej Los

v2

Add a Solution

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

2 solutions

Solution 2

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!

Posted 25-Apr-18 22:41pm

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Accepted Answer · 2018-04-25T17:42:00

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[^]