left join that does not give expected result

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)

left join that does not give expected result

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-01-26 : 09:25:57

Hi

I currently have this query...


SELECT     Text
FROM         dbo.tbl_Menu

This query give me 63 records, this is correct but then I need to add one more table that should match against NodeID and also filter on LanguageID, but if I do that I don't get 63 records, only 60 records. This is beacuse all NodeID is not available in the "MenuLanguage" table. But if it doesn't match I want the MenauLanguage.Name to be displayed as null, the MenuLanguage holds different languages for a menu and if there isn't a match against the NodeID it means I don't have a translation for that record.

Hope this make sense..


SELECT     dbo.tbl_Menu.Text, dbo.MenuLanguage.Name, dbo.MenuLanguage.LanguageID
FROM         dbo.tbl_Menu LEFT OUTER JOIN
                      dbo.MenuLanguage ON dbo.tbl_Menu.NodeId = dbo.MenuLanguage.NodeId
WHERE     (dbo.MenuLanguage.LanguageID = 2)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-26 : 09:29:53

Move the condition the where clause to the join.

SELECT     dbo.tbl_Menu.Text, dbo.MenuLanguage.Name, dbo.MenuLanguage.LanguageID
FROM         dbo.tbl_Menu LEFT OUTER JOIN
                      dbo.MenuLanguage ON dbo.tbl_Menu.NodeId = dbo.MenuLanguage.NodeId
     AND (dbo.MenuLanguage.LanguageID = 2)

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-01-26 : 09:40:34

Ahh, thanks a lot!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-26 : 10:53:54

see reason here

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources