Achievement Unlocked: JOIN help

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Achievement Unlocked: JOIN help

Author

Topic

FoogleDricks
Starting Member

2 Posts

Posted - 2010-04-17 : 08:02:33

So I have have two tables (This is modelled after XBlive achievements):

[Achievements]
id
name

[AchievementsReceived]
id
userid
achievementid

The [Achievements] data looks like this (I'll use colors as names for simplicty):
[1]["Red"]
[1]["Green"]
[1]["Blue"]

And the [AchievementsReceived] table is completely EMPTY because no users have received any achievements yet.

So this is my SELECT question:
"I'll give you a userID and you give me a list of ALL achievements with a column that indicates whether that user has that achievement"

This is my SELECT statement:
SELECT [Achievements].[Name],[AchievementsReceived].[UserID]
FROM [AchievementsReceived]
INNER join [Achievements] on [Achievements].[ID]=[AchievementsReceived].[AchievementID]
WHERE [AchievementsReceived].[UserID] =1

What I was hoping this dataset would look like this (The 'nulls" are because the [AchievementsReceived] table is empty, so this use has no achievements):

[Achievements].Name, [AchievementsReceived].userid
["Red"][null]
["Green"][null]
["Blue"][null]

The dataset I actually get back is EMPTY, which makes sense because there are no entries in the [AchievementsReceived] table. I can fix that by making an entry for every achievement for each user. That is what I did actually. But I hate that. How can I do this by only having an entry if the user has gotten the achievement?

There are several ways to make this work that I know of, but none of them are as good as getting back a single dataset that has what I need. Is there any way I can make a single SELECT statement that tells me status of all Achievements for one user?

I'll even change how my tables are configured if that makes it possible but I can't figure it out. Thanks for the help.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-17 : 08:06:39

Rewrite the query like this


SELECT	[Achievements].[Name],[AchievementsReceived].[UserID]
FROM	[AchievementsReceived] 
		LEFT JOIN [Achievements] ON [Achievements].[ID]=[AchievementsReceived].[AchievementID] 
		AND [AchievementsReceived].[UserID] =1

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-04-17 : 13:37:34

Use this query:


SELECT A.Name, AR.UserID
  FROM Achievements AS A
       LEFT OUTER JOIN
       AchievementsReceived AS AR
       ON AR.AchievementID = A.ID
          AND AR.UserID = 1

FoogleDricks
Starting Member

2 Posts

Posted - 2010-04-17 : 15:04:11

Thank you both very much. I appreciate your help. The second solution works perfectly. It's a sub-select, right? Exactly what I needed. Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources