How to return a list of things a user has not yet entered

[hapax]

I have two tables: 1) Games contains a list of games with data about each and 2) Comments has various user comments and ratings for the games.

I'm trying to return all the games for a user, where the user has not yet entered anything into the Comments table.

The following code is my best shot - but is returns several records for each game, since other people have Comments for those games. How do I return each game only once? (I'm using SQL Server 2005.)


SELECT C.GameID, C.UserID, C.CommentID,
(SELECT GameName FROM Games WHERE GameID = C.GameID ) as GameName,
(SELECT MinAge FROM Games WHERE GameID = C.GameID ) as MinAge
FROM Comments C
WHERE (UserID != @UserID)


Many thanks,
Mark

 

[johnherman]

Select G.GameID, G.USerID from Games G, Comments C
where G.USerID = C.UserID and Comments is null

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[johnherman]

Or, if the Comment record does not exist when there are no comments

Select G.GameID, G.USerID from Games G where
G.UserID || G.GameID not in (Select C.UserID || C.GameID from Comments C)


-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[JarlH]

My first thought was something like:
[tt]
SELECT somecolumns FROM Games
WHERE UserID NOT IN (SELECT UserID FROM Comments
WHERE UserID IS NOT NULL)
[/tt]

But you say you want to return all the games for a user, where the user has not yet entered anything into the Comments table, then you select commentID... Please explain.

 

[r937]

answer: LEFT OUTER JOIN with a condition

SELECT G.GameName 
  FROM Games as G
LEFT OUTER
  JOIN Comments as C
    ON C.GameID = G.GameID
   AND C.UserID = @UserID
 WHERE C.GameID IS NULL

r937.com | rudy.ca