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-09 : 17:23:42
|
| I have two tables:Game and GamePopularity.Game has the columns ID, Title, Author, Description, Popularity.GamePopularity has the columns ID, GameID, UserName, and Popularity.The Game.ID number and GamePopularity.GameID are the same.My current SQL Statement looks like the following:"SELECT * FROM Game G INNER JOIN GamePopularity GP ON G.Popularity=GP.Popularity WHERE G.ID=" & ID & ""This should give one record that has merged the Popularity fields. The problem is that there is going to be more than one GamePopularity entry with the same GameID field, so in order to differentiate the query also needs to take into consideration the UserName. Thus I need something like this:"SELECT * FROM Game G INNER JOIN GamePopularity GP ON G.Popularity=GP.Popularity WHERE G.ID=" & ID & "AND GP.UserName LIKE " & varUserName & ""Okay, fine and dandy if there is a GamePopularity entry for every game. But in this case many of the games don't have popularity ratings, so no title is displayed. So how do I tell the database that I want the entry from Game no matter what but that I only want the information from GP.Popularity combined with G.Popularity WHERE GP.UserName is equal to varUserName?Thanks in advance.David.- http://www.civilwarsearch.com/- http://www.thehungersite.com/- http://www.grid.org/ |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-06-09 : 17:31:48
|
use left join??Go with the flow & have fun! Else fight the flow  |
 |
|
|
davidshq
Posting Yak Master
119 Posts |
Posted - 2005-06-09 : 18:01:42
|
| That works great, except for one thing. See, some entries have an entry but they were written by a different user, therefore I don't want them to appear for another user. So I need to somehow make the joining of the Game.Popularity field with the GamePopularity.Popularity field contingent on the GamePopularity.UserName being the same as the actual user. Example:User1 rates game1, game2, game3.User2 rates game4.I want the SQL to display all games for user2 and integrate the GamePopularity.Popularity field into Game.Popularity but only where the user is user2. So, from game1, game2, game3 it should select the original data without a join, and only for game4 make the join.David.- http://www.civilwarsearch.com/- http://www.thehungersite.com/- http://www.grid.org/ |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-06-09 : 18:04:46
|
| with a left join you get nulls where there's no matching data. you can handle those nulls any way you like.if you still have problems post Create table and insert into statments with sample data and desired results based on given sample data.Go with the flow & have fun! Else fight the flow |
|
|
|
davidshq
Posting Yak Master
119 Posts |
Posted - 2005-06-10 : 15:46:20
|
| 1. Create Tables:create table "games"("ID" "int","Title" "string","Description" "string","Popularity" "int")create table "popularity"("ID" "int","userID" "string","gameID" "int","Popularity" "int")2. Insert Statements:insert into games ("title", "description") VALUES ("dodgeball", "a game")insert into games ("title", "description") VALUES ("baseball", "a game")insert into popularity ("userID", "gameID", "popularity") VALUES ("user1", "1", "8")insert into popularity ("userID", "gameID", "popularity") VALUES ("user2", "1", "10")3. Query:select * from games G inner join popularity P ON g.popularity=p.popularity WHERE ID=varID AND varUserName=userID AND gameID=gameID4. Results: Works fine for dodgeball, but for baseball it gives back no results.5. Desired Results: Give back results from the game table WHERE ID=varID even if varUserName=userID and gameID=gameID don't exist.- http://www.civilwarsearch.com/- http://www.thehungersite.com/- http://www.grid.org/ |
|
|
|
davidshq
Posting Yak Master
119 Posts |
Posted - 2005-06-13 : 09:25:36
|
| I figured this one out. Here is my final code:"SELECT * FROM Game G LEFT JOIN GamePopularity GP ON G.Popularity = GP.Popularity WHERE (G.ID=" & ID & " AND GP.gID=" & ID & " AND GP.uID LIKE '" & varUserName & "') OR (G.ID=" & ID & " AND GP.gID IS NULL)"David.- http://www.civilwarsearch.com/- http://www.thehungersite.com/- http://www.grid.org/ |
|
|
|
|
|
|
|
|
Select...