Solved

Help with constructing SQL Statement

Posted on 2009-04-01
9
203 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
  • 5
  • 4
9 Comments
 
LVL 26

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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 26

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 26

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 26

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now