Solved

Help with constructing SQL Statement

Posted on 2009-04-01
9
214 Views
Last Modified: 2012-05-06
This is sorta a 2 part question although I am not sure if I should put them as one question...oh well ;)

FIRST
I am after a bit of help constructing a SQL query to return the correct results which are to populate a dropdown list from a StorProc. Its part of site for adding scores for squash matches and the dropdown is supposed to list all players in your division that you have NOT played a game against yet. When a game is played someone would log into the site, select the person from a dropdown list and enter scores for both sides (either player can do this). The script creates a game, takes the new gameID, adds the points from that match to the points total in the table 'nykSqPlayers' and populates the rest of the relevant tables below and then refreshes the dropdown. However, after initially entering the score for Test User1 against Test User2 the dropdown isn't listing anyone. So I tested the SQL below in Server Management and it doesn't return any data. It should return Test User 3 and Test User 4 (in theory).

Format will be:

Tables (Fields)
Data

nykGame (ID int IDENTITY, Date dateimte, UserID int)
25, 01/04/2009 13:41:53, 3

nykScore (ID int IDENTITY, nykGameID int, UserID int, Score int, Points int)
21, 25, 3, 3, 5
22, 25, 4, 1, 2

nykSqPlayers (UserID int, DivisionID int, UserName nvarchar(250), Points int)
3, 1, Test User1, 5
4, 1, Test User2, 2
5, 1, Test User3, 0
6, 1, Test User4, 0

What I have managed so far is (replace @UserID with the relevant UserID from nykSqPlayers).:

            SELECT nykSqPlayers.*, nykScore.nykGameID
            FROM nykScore RIGHT OUTER JOIN nykSqPlayers ON nykScore.UserID = nykSqPlayers.UserId
            WHERE DivisionId= (select  DivisionId from dbo.nykSqPlayers where UserId= @UserID) AND nykSqPlayers.UserId <> @UserID AND nykGameID NOT IN (SELECT nykGameID FROM nykScore WHERE UserID=@UserID) AND nykSqPlayers.UserId NOT IN (SELECT nykScore_1.UserID AS OUserID FROM Users AS Users_1 INNER JOIN nykScore AS nykScore_1 ON Users_1.UserID = nykScore_1.UserID INNER JOIN nykScore INNER JOIN Users ON nykScore.UserID = Users.UserID INNER JOIN nykGame ON nykScore.nykGameID = nykGame.ID ON nykScore_1.nykGameID = nykGame.ID WHERE (nykScore.UserID = @UserID) AND (nykScore_1.UserID <> @UserID))

My 2nd request is:

Is it possible to constract a score table setup purely through SQL so it would return something like:

                       |  Test User1  | Test User2  |  Test User3  |  Test User4  |  Total Points
------------------------------------------------------------------------------------------------
Test User 1    |     N/A           |      3-1         |       Null         |          Null      |       5
------------------------------------------------------------------------------------------------
Test User 2   |        1-3         |       N/A       |      Null          |        Null         |       2
------------------------------------------------------------------------------------------------
Test User 3   |      Null          |      Null          |      Null          |      Null          |       0
------------------------------------------------------------------------------------------------
Test User4    |      Null          |      Null          |      Null          |      Null          |       0
------------------------------------------------------------------------------------------------

Any help with this would be greatly appreciated...been working on this through the night ;)
0
Comment
Question by:nyk001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24045181
This query will give you the opponents in your division that you have not played yet.
I set up a test db with the tables and data you described and it works there returning this:
UserID      DivisionID      UserName      Points
5      1      Test User3      0
6      1      Test User4      0
I will look at your second request but would have liked it better as a second request as these take a little work.  (hey, I like the points, I would like to be a Master some day) :-)
DECLARE @UserID INT 
SET @UserID = 3;
 
SELECT ThisPlayer.*
FROM nykSqPlayers ThisPlayer
WHERE ThisPlayer.UserID <> @UserID
AND ThisPlayer.DivisionID = (SELECT NSP2.DivisionID FROM nykSqPlayers NSP2 WHERE NSP2.UserID = @UserID)
EXCEPT
SELECT Opponent.*
FROM nykSqPlayers ThisPlayer
INNER JOIN nykGame NG ON ThisPlayer.UserID = NG.UserID
INNER JOIN nykScore NS ON NS.nykGameID = NG.ID AND NS.UserID <> ThisPlayer.UserID
INNER JOIN nykSqPlayers Opponent ON NS.UserID = Opponent.UserID
WHERE ThisPlayer.UserID = @UserID

Open in new window

0
 

Author Comment

by:nyk001
ID: 24047481
CGLuttrell.

I can always create a 2nd thread with the additional request on it and then link to it from this thread to make sure theres relation (just incase it was ever used/needed later on).

As for your SQL it works perfectly fine IF you use the ID of Test User1...but if you use the ID of Test User2 (which is 4) it returns the players Test User1, Test User3, Test User4....but as there has already been a game between Test User1 and Test User2, we shouldn't see Test User1 in the data that is returned.

It should work for any players ID you use...return the list of players you havent had a game against yet.
0
 

Author Comment

by:nyk001
ID: 24047720
I think I forgot to add (which I am VERY sorry) the relationship of the tables.

nykGame.ID -> nykScore.nykGameID / nykScore.UserID -> nykSqPlayers.UserID

The UserID in nykGame plays no role in this situation...its only there to act as identifying who entered the scores for both players...
0
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 

Author Comment

by:nyk001
ID: 24047763
Soooo...to recap.

A player submits the score for BOTH parties.

An entry is made into nykGame (along with the UserID of the user who submitted the scores).

The ID of the newly created nykGame record is taken and passed into nykScore.nykGameID along with the other relevant info to populate 2 records. One for Player 1 and the other for his/her opponent. In this case Test User1 and Test User2. Then the Points field for each user is updated to ADD the points from that game onto their already existing points. (points = points+x).

So for each score submitted 1 nykGame record is created. 2 nykScore entries are created (for each player in teh game).

Hope this makes more sense. I am sorry to have missed these elements out.
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24054227
Thanks for the info that helped.  I was using the userID in nykGame incorrectly.  I appologize for not running the reverse test and catching that obvious oversight of going from Test User2.  The following query was run through all 4 test users and retruned the correct 2 or 3 opponents left to play.  I have used very descriptive aliases to help understand what I was doing, of course you can shorten those if you like.
I also attached the complete script I used to set this up in case that will help you.  You can look at the last query I am still working on for the grid output you want.  I am just trying to figure out how to use PIVOT to make it into the grid.  The problem is that the number of players will be dynamic I am sure and you want the Player Names in the output.
SELECT ThisPlayer.*
FROM nykSqPlayers ThisPlayer
WHERE ThisPlayer.UserID <> @UserID
AND ThisPlayer.DivisionID = (SELECT NSP2.DivisionID FROM nykSqPlayers NSP2 WHERE NSP2.UserID = @UserID)
EXCEPT
SELECT Opponent.*
FROM nykSqPlayers ThisPlayer
INNER JOIN nykScore ThisPlayerScores ON ThisPlayerScores.UserID = ThisPlayer.UserID
INNER JOIN nykGame NG ON ThisPlayerScores.nykGameID = NG.ID
INNER JOIN nykScore NS ON NS.nykGameID = NG.ID AND NS.UserID <> ThisPlayer.UserID
INNER JOIN nykSqPlayers Opponent ON NS.UserID = Opponent.UserID
WHERE ThisPlayer.UserID = @UserID

Open in new window

ComplexJoin-and-Grid-Query.txt
0
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 500 total points
ID: 24057083
Here ia a script that creates the grid.  It is not dynamic yet.  I will try to add that next.
SELECT ThisPlayerId, ThisPlayer, 
		MIN([Test User1]) [Test User1], 
		MIN([Test User2]) [Test User2], 
		MIN([Test User3]) [Test User3], 
		MIN([Test User4]) [Test User4]
FROM 
(SELECT ThisPlayer.UserID ThisPlayerId,
	   ThisPlayer.UserName ThisPlayer,
	   ThisPlayer.Points,
	   Opponent.UserName Opponent,
	   ThisPlayerScore.nykGameID, 
	   CASE WHEN ThisPlayer.UserID = Opponent.UserId THEN 'N/A' ELSE CONVERT(VARCHAR,ThisPlayerScore.Score)+'-'+CONVERT(VARCHAR,OpponentScore.Score) END Score
FROM nykSqPlayers ThisPlayer
CROSS JOIN nykSqPlayers Opponent 
LEFT OUTER JOIN dbo.nykScore ThisPlayerScore
INNER JOIN dbo.nykScore OpponentScore ON ThisPlayerScore.nykGameID = OpponentScore.nykGameID AND ThisPlayerScore.UserID <> OpponentScore.UserID
ON ThisPlayer.UserID = ThisPlayerScore.UserID AND Opponent.UserID = OpponentScore.UserID
WHERE ThisPlayer.DivisionID = @DivisionId
AND Opponent.DivisionID = @DivisionId) AS P
PIVOT (MIN(Score) FOR Opponent IN ( [Test User1],[Test User2],[Test User3],[Test User4])) AS pvt
GROUP BY ThisPlayerId, ThisPlayer

Open in new window

0
 

Author Comment

by:nyk001
ID: 24057670
Wow you're really going to town on this ;)

I'm need to really dig into what you're producing to understand it so if you have any brief explanations on what you've done I would love to hear.

Kinda just don't wanna be one of those people that just takes a solution and runs...I want to learn ;)

Appreciate the work you're doing CGLuttrell
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24080985
nyk001,
I have not abandoned you here, I will try to do some explanations as soon as I can.  It's just the real job and life get in the way.  I love solving these type of business logic problems which is why I do this.
0
 

Author Comment

by:nyk001
ID: 24085099
Totally understand...I'm the same (full time job and 2 kids...oh and the wife :P ).

I'm playing around with what you've given me anyways and also reading up on elements to get a better understanding. With a bit of a luck I may even be able to post some ideas myself ;)
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question