Possible to avoid 2 joins on same table ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2008 Forums

Transact-SQL (2008)

Possible to avoid 2 joins on same table ?

Author

Topic

grimmus
Yak Posting Veteran

53 Posts

Posted - 2011-11-15 : 04:54:02

Hi, I am querying a team statistics table that contains language codes for source and target languages (i.e. eng,esp and so on)

I'm doing a join on the languages table so the language description is being outputted instead of the code (i.e. English instead of eng)

Currently,I am doing 2 joins on the language table because the team stats table has source and target language columns.

Is it possible to avoid using 2 joins for this ?

Thanks

SELECT T.UserID, src.Description AS SourceLang, tgt.Description AS TargetLang, T.Subject
FROM TeamStats T

JOIN Languages src ON src.L_ID = T.SrcLangCode
JOIN Languages tgt ON tgt.L_ID = T.TgtLangCode
WHERE UserID = 2174

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-15 : 04:58:04

This is the right way - nothing to change.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-15 : 07:26:13

Just to show you that there's a way, but not sure whether it would perform better that your original suggestion. So test it and see before you use this


SELECT UserID,Subject,
MAX(CASE WHEN LangCode='SrcLangCode' THEN Description ELSE NULL END) AS SourceLang,
MAX(CASE WHEN LangCode='TgtLangCode' THEN Description ELSE NULL END) AS TargetLang
FROM
(
SELECT UserID,Subject,LangCode,Val
FROM TeamStats t
UNPIVOT (Val FOR LangCode IN ([SrcLangCode],[TgtLangCode]))u
)m
INNER JOIN Languages lan
ON lan.L_ID = m.Val
GROUP BY UserID,Subject

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-15 : 07:41:43

I want to eat a cake and a potato - is it possible to avoid to use the same mouth?

Just to show you that there's a way:
You can eat the cake and then open your belly using a knife, put in the potato and then close the wound.

No, you're never too old to Yak'n'Roll if you're too young to die.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-15 : 11:20:38

quote:
Originally posted by webfred

I want to eat a cake and a potato - is it possible to avoid to use the same mouth?

Just to show you that there's a way:
You can eat the cake and then open your belly using a knife, put in the potato and then close the wound.

No, you're never too old to Yak'n'Roll if you're too young to die.

Classic :)

BruceT
Yak Posting Veteran

78 Posts

Posted - 2011-11-15 : 13:39:28

How about this one! I want to get to the other side of the road, but I don't want to cross the road?
Simple, turn around and walk the other way.....a long long way! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources