Joins here there and everywhere....

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

General SQL Server Forums

New to SQL Server Programming

Joins here there and everywhere....

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-09-12 : 11:47:23

Here is my first query:

SELECT SUM(CHELTONCUSTOMIZATIONS.DBO.SOMODSRF.PRICEMOD)
      FROM   CHELTONCUSTOMIZATIONS.DBO.SOMODSRF
       INNER JOIN M2MDATA01.DBO.SORELS
         ON CHELTONCUSTOMIZATIONS.DBO.SOMODSRF.FKSORELSID = M2MDATA01.DBO.SORELS.IDENTITY_COLUMN
       INNER JOIN M2MDATA01.DBO.SOITEM
         ON M2MDATA01.DBO.SOITEM.FSONO = M2MDATA01.DBO.SORELS.FSONO
            AND M2MDATA01.DBO.SOITEM.FINUMBER = M2MDATA01.DBO.SORELS.FINUMBER
       INNER JOIN M2MDATA01.DBO.SOMAST
         ON DBO.SOITEM.FSONO = M2MDATA01.DBO.SOMAST.FSONO
WHERE  YEAR(CHELTONCUSTOMIZATIONS.DBO.SOMODSRF.DATE) = YEAR(GETDATE())
       AND CHELTONCUSTOMIZATIONS.DBO.SOMODSRF.PRICEMOD > 0

Here is the change I want to make in it:

SELECT SUM(CHELTONCUSTOMIZATIONS.DBO.SOMODSRF.PRICEMOD)
FROM   CHELTONCUSTOMIZATIONS.DBO.SOMODSRF
       LEFT OUTER JOIN M2MDATA01.DBO.SORELS
         ON CHELTONCUSTOMIZATIONS.DBO.SOMODSRF.FKSORELSID = M2MDATA01.DBO.SORELS.IDENTITY_COLUMN
       INNER JOIN M2MDATA01.DBO.SOITEM
         ON M2MDATA01.DBO.SOITEM.FSONO = M2MDATA01.DBO.SORELS.FSONO
            AND M2MDATA01.DBO.SOITEM.FINUMBER = M2MDATA01.DBO.SORELS.FINUMBER
       INNER JOIN M2MDATA01.DBO.SOMAST
         ON DBO.SOITEM.FSONO = M2MDATA01.DBO.SOMAST.FSONO
WHERE  YEAR(CHELTONCUSTOMIZATIONS.DBO.SOMODSRF.DATE) = YEAR(GETDATE())
       AND CHELTONCUSTOMIZATIONS.DBO.SOMODSRF.PRICEMOD > 0

These yield the same result, but definitely shouldn't. Can someone point me in the right direction, perhaps using parenthesis, to make this rogue query behave?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-12 : 13:12:46

You haven't provided enough information for us to help you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-09-12 : 14:03:54

Well, basically what this means is I want a left outer join between cheltoncustomizations.dbo.somodsrf and the rest of the query.

I think I've got it with a derived table.

This is where I am now:

SELECT SUM(CHELTONCUSTOMIZATIONS.DBO.SOMODSRF.PRICEMOD)
FROM   CHELTONCUSTOMIZATIONS.DBO.SOMODSRF
       LEFT OUTER JOIN (SELECT M2MDATA01.DBO.SOITEM.[FPRODCL],
                               M2MDATA01.DBO.SORELS.IDENTITY_COLUMN
                        FROM   M2MDATA01.DBO.SORELS
                               INNER JOIN M2MDATA01.DBO.SOITEM
                                 ON M2MDATA01.DBO.SOITEM.FSONO = M2MDATA01.DBO.SORELS.FSONO
                                    AND M2MDATA01.DBO.SOITEM.FINUMBER = M2MDATA01.DBO.SORELS.FINUMBER
                               INNER JOIN M2MDATA01.DBO.SOMAST
                                 ON DBO.SOITEM.FSONO = M2MDATA01.DBO.SOMAST.FSONO) AS M2M
         ON CHELTONCUSTOMIZATIONS.DBO.SOMODSRF.FKSORELSID = M2M.IDENTITY_COLUMN
WHERE  YEAR(CHELTONCUSTOMIZATIONS.DBO.SOMODSRF.DATE) = YEAR(GETDATE())
       AND CHELTONCUSTOMIZATIONS.DBO.SOMODSRF.PRICEMOD > 0

I found Jeff's blog entry http://weblogs.sqlteam.com/jeffs/archive/2007/10/11/mixing-inner-outer-joins-sql.aspx

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-09-12 : 16:25:35

If you left outer join one table, you need to left outer join all child tables that you link to it. Otherwise it effectively reverts to an inner join.

Boycotted Beijing Olympics 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources