Help with constructing SQL Statement

[FateFirst]

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

Almost forgot a brief explanation of 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.

- FateFirst

 

[FateFirst]

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 in nykSqPlayers for each user is updated to ADD the points from the recent 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) and 2 records are UPDATED in nykSqPlayers.

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

- FateFirst

 

[hmckillop]

A quick question, why do you store your "match" results in a table with two seperate rows. If there is a specific reason please state so, otherwise I would recommend changing this to be

MatchID int (Identity)
Player1 int --FK to UserID
Player2 int --FK to UserID
Player1Score int
Player2Score int
Player1Points
Player2Points

The reason being is that a match is a single entity, it consists of the above data.

Cheers

"I'm living so far beyond my income that we may almost be said to be living apart

 

[hmckillop]

One more question , why do you want to generate the table from SQL (which it can be done with alot of work), when you can return all results and users back to whatever application and then let it build the table up.
e.g. for this sort of thing I would write a quick bit of Excel VBA to populate a spreadsheet.


For anyone else having a go at this - here is some code to save you scripting tables etc

CREATE TABLE nykGame (ID int IDENTITY, myDate datetime, UserID int)
INSERT INTO nykGame (mydate, userid)
SELECT getdate(), 3

CREATE TABLE nykScore (ID int IDENTITY, nykGameID int, UserID int, Score int, Points int)
INSERT INTo nykScore (nykGameID, UserID, Score, Points)
SELECT 1, 3, 3, 5
UNION
SELECT 1, 4, 1, 2

CREATE TABLE nykSqPlayers (UserID int, DivisionID int, UserName nvarchar(250), Points int)
INSERT INTO nykSqPlayers (UserID, DivisionID, UserName, Points)
SELECT 3, 1, 'Test User1', 5
UNION
SELECT 4, 1, 'Test User2', 2
UNION 
SELECT 5, 1, 'Test User3', 0
UNION
SELECT 6, 1, 'Test User4', 0


select * from nykGame
select * from nykScore
select * from nykSqPlayers


DROP TABLE nykGame 
DROP TABLE nykScore
DROP TABLE nykSqPlayers

"I'm living so far beyond my income that we may almost be said to be living apart

 

[FateFirst]

I personally didn't code this. It is part of a DotNetNuke asp.net module we hired someone to develop...however this developer let us down so I'm having to just takeover.

I assume the reason match results are in 2 different rows is because either player can enter the score for BOTH sides. Having it in one table/entity would would mean having to define who is actually Player 1 or Player 2.

Then when 'Player X' logged into the site to check the scores we would need to check both ID fields for his ID because you wouldn't know which field contained 'Player X' ID.

As for the score table...I only asked because I have no idea how to display such a thing in asp.net. I'm relatively new to it all and trying to learn straight away...so thought if a complex query could do the work and I just need to dump it to a gridview then why not for now

- FateFirst

 

[FateFirst]

I am more than happy to try and change the setup of the tables that hold the info to speed things along.

Any suggestions are welcome. I agree that above does seem over-complicated.

- FateFirst