Left outer join with condition

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 outer join with condition

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-01-10 : 10:21:49

Hi

I have the following Query...


SELECT        dbo.SPRAKBANMTEXT.SPRAK, dbo.ARTIKEL.ARTIKELNR, dbo.SPRAKBANMTEXT.ANMARKNING, dbo.ARTIKEL.TEXTNRANMSPRAKB, dbo.ARTIKEL.TEXTNRANMSPRAKO, dbo.ARTIKEL.TEXTNRBENAMN
FROM            dbo.ARTIKEL LEFT OUTER JOIN
                         dbo.SPRAKOANMTEXT ON dbo.ARTIKEL.TEXTNRANMSPRAKO = dbo.SPRAKOANMTEXT.TEXTNRANMSPRAKO LEFT OUTER JOIN
                         dbo.SPRAKBANMTEXT ON dbo.ARTIKEL.TEXTNRANMSPRAKB = dbo.SPRAKBANMTEXT.TEXTNRANMSPRAKB

My issue is that I need to have a left outer join on SPRAKBANMTEXT, to get the rows I need but in the same time have a condition which is... If there is a ARTIKEL.TEXTNRANMSPRAKB AND It match dbo.SPRAKBANMTEXT.TEXTNRANMSPRAKB I need to filter based on SPRAKBANMTEXT.SPRAK = "GB"

How can I do that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-10 : 23:57:49

it should be this


SELECT        dbo.SPRAKBANMTEXT.SPRAK, dbo.ARTIKEL.ARTIKELNR, dbo.SPRAKBANMTEXT.ANMARKNING, dbo.ARTIKEL.TEXTNRANMSPRAKB, dbo.ARTIKEL.TEXTNRANMSPRAKO, dbo.ARTIKEL.TEXTNRBENAMN
FROM            dbo.ARTIKEL LEFT OUTER JOIN
                         dbo.SPRAKOANMTEXT ON dbo.ARTIKEL.TEXTNRANMSPRAKO = dbo.SPRAKOANMTEXT.TEXTNRANMSPRAKO LEFT OUTER JOIN
                         dbo.SPRAKBANMTEXT ON dbo.ARTIKEL.TEXTNRANMSPRAKB = dbo.SPRAKBANMTEXT.TEXTNRANMSPRAKB
                         AND dbo.SPRAKBANMTEXT.SPRAK = 'GB'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-10 : 23:58:31

Here's explanation on the same
http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-01-11 : 03:10:25

Thank you very much for the code and the explanation!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-11 : 04:54:35

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources