Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
davidshq
Posting Yak Master
119 Posts |
Posted - 2005-06-13 : 12:21:35
|
Here is my first table with sample data:INSERT INTO Game (title, description) VALUES (Dodgeball, Game)INSERT INTO Game (title, description) VALUES (Baseball, Game)Here is the second table w/sample data:INSERT INTO LastPlayed (gameID, userID, LastPlayedDate) VALUES (1, jdoe, 5/9/2005)INSERT INTO LastPlayed (gameID, userID, LastPlayedDate) VALUES (1, mdoe, 5/9/2005)Here is my current SQL Statement:SELECT * FROM Game G LEFT JOIN GamePopularity GP ON G.YourPopularity = GP.Popularity WHERE (G.ID=ID AND GP.gID=ID AND GP.uID LIKE 'varUserName') OR (G.ID=ID AND GP.gID IS NULL)What this statement does is as follows:1. Selects a game where the ID equals an inputted ID.2. Integrates the Popularity rating if it coincides with the GameID and the UserID (you don't want someone elses popularity rating popping up).3. Still shows results that don't have a Popularity rating.The problem is that I don't know how to make it show those games which have a popularity rating but it isn't for the given UserID and thus make it show the Game but not integrate the Popularity.So, what I want to happen is: Joe does a search for games, all games pop up with the matching ID, but only his rating is inserted into the Popularity field.What it does now however is skips over any games where there is a Popularity rating but it is not Joes. Instead it should still show the game but just not integrate the Popularity rating.Any suggestions?David.- http://www.civilwarsearch.com/- http://www.thehungersite.com/- http://www.grid.org/ |
|
nathans
Aged Yak Warrior
938 Posts |
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-06-13 : 18:02:52
|
you cannot put criteria in your WHERE clause involving outer tables or it excludes NULL (missing) values on the outer join.- Jeff |
|
|
davidshq
Posting Yak Master
119 Posts |
|
|
|
|
|
|