SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Including Info. From One Table, Excluding From....
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

davidshq
Posting Yak Master

USA
119 Posts

Posted - 06/13/2005 :  12:21:35  Show Profile  Visit davidshq's Homepage  Reply with Quote
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

USA
938 Posts

Posted - 06/13/2005 :  15:59:39  Show Profile  Reply with Quote
Is [YourPopularity] a column of table [game]?

quote:
FROM Game G LEFT JOIN GamePopularity GP ON G.YourPopularity = GP.Popularity


Please post some code that will run in QA.

Follow these guidelines, very helpful:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 06/13/2005 :  18:02:52  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
you cannot put criteria in your WHERE clause involving outer tables or it excludes NULL (missing) values on the outer join.

- Jeff

Edited by - jsmith8858 on 06/13/2005 18:03:13
Go to Top of Page

davidshq
Posting Yak Master

USA
119 Posts

Posted - 06/14/2005 :  11:15:52  Show Profile  Visit davidshq's Homepage  Reply with Quote
I figured this one out by using stored procedures, thanks.
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000