SQL joining two tables from different databases and syntax

Currently I have the following statement that works but is multiplying my quantities by the number of rows from my second table rbeacon.dbo.binlocat.

This more complicated than statements I normally do and was hoping someone can see a glaring issue with my syntax.

SELECT     SIF.dbo.OITW.ItemCode, SUM(SIF.dbo.OITW.OnHand) AS 'OH', isnull (SUM(CASE WHEN SIF.dbo.oitw.whscode = '05' THEN SIF.dbo.oitw.onhand END),0) AS '05OH',
                      isnull((SUM(CASE WHEN SIF.dbo.oitw.whscode = '10' THEN SIF.dbo.oitw.onhand END)- SUM(CASE WHEN rbeacon.dbo.binlocat.BINLABEL = '10SD'  THEN rbeacon.dbo.binlocat.QUANTITY END)- SUM(CASE WHEN rbeacon.dbo.binlocat.BINLABEL like '10WA%' THEN rbeacon.dbo.binlocat.QUANTITY END)),0)  AS '10OH',
                      isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '15' THEN SIF.dbo.oitw.onhand END),0) AS '15OH',
                      isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '20' THEN SIF.dbo.oitw.onhand END),0) AS '20OH',
                      isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '30' THEN SIF.dbo.oitw.onhand END),0) AS '30OH',
                      isnull(Sum(CASE WHEN SIF.dbo.oitw.whscode = '05' THEN SIF.dbo.oitw.OnOrder END),0) AS '05OO',
                      isnull(Sum(CASE WHEN SIF.dbo.oitw.whscode = '10' THEN SIF.dbo.oitw.OnOrder END),0) AS '10OO',
                      isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '10' THEN SIF.dbo.oitw.IsCommited END),0) AS '10Alloc',
                      isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '20' THEN SIF.dbo.oitw.IsCommited END),0) AS '20Alloc',
                      isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '30' THEN SIF.dbo.oitw.IsCommited END),0) AS '30Alloc',
                      isnull(SUM(CASE WHEN rbeacon.dbo.binlocat.BINLABEL = '10SD' OR rbeacon.dbo.binlocat.BINLABEL like '10WA%' THEN rbeacon.dbo.binlocat.QUANTITY END),0) AS '10SD'
                     FROM         SIF.dbo.OITW LEFT OUTER JOIN
                      BINLOCAT ON SIF.dbo.OITW.ItemCode = BINLOCAT.PRODUCT COLLATE SQL_Latin1_General_CP850_CI_AS
GROUP BY SIF.dbo.OITW.ItemCode
notasgoodasyouAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
We need to know more about your tables. How are the PK and FK defined?
Also, do you really need a LEFT OUTER JOIN? Why not an INNER JOIN instead?
0
Scott PletcherSenior DBACommented:
SELECT     SIF.dbo.OITW.ItemCode, SUM(SIF.dbo.OITW.OnHand) AS 'OH', isnull (SUM(CASE WHEN SIF.dbo.oitw.whscode = '05' THEN SIF.dbo.oitw.onhand END),0) AS '05OH',
                       isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '10' THEN SIF.dbo.oitw.onhand END) -
                             SUM(BINLOCAT2.[10SD]) - SUM(BINLOCAT2.[10WA]), 0) AS '10OH',
                       isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '15' THEN SIF.dbo.oitw.onhand END),0) AS '15OH',
                       isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '20' THEN SIF.dbo.oitw.onhand END),0) AS '20OH',
                       isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '30' THEN SIF.dbo.oitw.onhand END),0) AS '30OH',
                       isnull(Sum(CASE WHEN SIF.dbo.oitw.whscode = '05' THEN SIF.dbo.oitw.OnOrder END),0) AS '05OO',
                       isnull(Sum(CASE WHEN SIF.dbo.oitw.whscode = '10' THEN SIF.dbo.oitw.OnOrder END),0) AS '10OO',
                       isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '10' THEN SIF.dbo.oitw.IsCommited END),0) AS '10Alloc',
                       isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '20' THEN SIF.dbo.oitw.IsCommited END),0) AS '20Alloc',
                       isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '30' THEN SIF.dbo.oitw.IsCommited END),0) AS '30Alloc',
                       isnull(SUM(binlocat2.[10SD] + binlocat2.[10WA]),0) AS '10SD'
                      FROM         SIF.dbo.OITW LEFT OUTER JOIN (
                           SELECT      PRODUCT COLLATE SQL_Latin1_General_CP850_CI_AS,
                                       SUM(CASE WHEN BINLABEL = '10SD' THEN QUANTITY ELSE 0 END) AS [10SD],
                                       SUM(CASE WHEN BINLABEL LIKE '10WA%' THEN QUANTITY ELSE 0 END) AS [10WA]
                                  FROM BINLOCAT
                           GROUP BY PRODUCT
                      ) AS BINLOCAT2 ON SIF.dbo.OITW.ItemCode = BINLOCAT2.PRODUCT
 GROUP BY SIF.dbo.OITW.ItemCode
0
notasgoodasyouAuthor Commented:
Victor,

The 2 tables are completely unrelated as far as keys are concerned.  It happens that SIF.dbo.OITW.Itemcode = Rbeacon.dbo.binlocat.product.  These are from two different systems and I am trying to get some net numbers on inventory.  The problem I am running into is that base on Itemcode and Product both tables have multiple rows so the values when combining them are getting multiplied.

Scott,

You answer was close but really just reversed my problem.  Now the 10SD value was getting multiplied as  it is getting summed when added to all rows of the SIF.dbo.OITW.Itemcode so if the group by is getting 4 rows of data the value for 10SD is 4x what it actually is in the sub query.  I made a change as you left of field name and I combined a case but the results are the same your way or mine.  


SELECT     SIF.dbo.OITW.ItemCode, SUM(SIF.dbo.OITW.OnHand) AS 'OH', isnull (SUM(CASE WHEN SIF.dbo.oitw.whscode = '05' THEN SIF.dbo.oitw.onhand END),0) AS '05OH',
                        isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '10' THEN SIF.dbo.oitw.onhand END) -
                             SUM(BINLOCAT2.[10SD]), 0) AS '10OH',
                        isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '15' THEN SIF.dbo.oitw.onhand END),0) AS '15OH',
                        isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '20' THEN SIF.dbo.oitw.onhand END),0) AS '20OH',
                        isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '30' THEN SIF.dbo.oitw.onhand END),0) AS '30OH',
                        isnull(Sum(CASE WHEN SIF.dbo.oitw.whscode = '05' THEN SIF.dbo.oitw.OnOrder END),0) AS '05OO',
                        isnull(Sum(CASE WHEN SIF.dbo.oitw.whscode = '10' THEN SIF.dbo.oitw.OnOrder END),0) AS '10OO',
                        isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '10' THEN SIF.dbo.oitw.IsCommited END),0) AS '10Alloc',
                        isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '20' THEN SIF.dbo.oitw.IsCommited END),0) AS '20Alloc',
                        isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '30' THEN SIF.dbo.oitw.IsCommited END),0) AS '30Alloc',
                        isnull(SUM(binlocat2.[10SD]),0) AS '10SD'
                       FROM         SIF.dbo.OITW LEFT OUTER JOIN (
                            SELECT      Rbeacon.dbo.binlocat.PRODUCT COLLATE SQL_Latin1_General_CP850_CI_AS as Product,
                                        SUM(CASE WHEN Rbeacon.dbo.binlocat.BINLABEL = '10SD'OR Rbeacon.dbo.binlocat.BINLABEL LIKE '10WA%' THEN Rbeacon.dbo.binlocat.QUANTITY ELSE 0 END) AS [10SD]
                                   FROM Rbeacon.dbo.BINLOCAT
                            GROUP BY Rbeacon.dbo.binlocat.PRODUCT
                       ) AS BINLOCAT2 ON SIF.dbo.OITW.ItemCode = BINLOCAT2.PRODUCT
  GROUP BY SIF.dbo.OITW.ItemCode
0
Scott PletcherSenior DBACommented:
Sorry.   I should have changed the SUM() on the BINLOCAT2 values to MAX() instead:

 SELECT     SIF.dbo.OITW.ItemCode, SUM(SIF.dbo.OITW.OnHand) AS 'OH', isnull (SUM(CASE WHEN SIF.dbo.oitw.whscode = '05' THEN SIF.dbo.oitw.onhand END),0) AS '05OH',
                         isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '10' THEN SIF.dbo.oitw.onhand END), 0) -
                              MAX(BINLOCAT2.[10SD])
AS '10OH',
                         isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '15' THEN SIF.dbo.oitw.onhand END),0) AS '15OH',
                         isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '20' THEN SIF.dbo.oitw.onhand END),0) AS '20OH',
                         isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '30' THEN SIF.dbo.oitw.onhand END),0) AS '30OH',
                         isnull(Sum(CASE WHEN SIF.dbo.oitw.whscode = '05' THEN SIF.dbo.oitw.OnOrder END),0) AS '05OO',
                         isnull(Sum(CASE WHEN SIF.dbo.oitw.whscode = '10' THEN SIF.dbo.oitw.OnOrder END),0) AS '10OO',
                         isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '10' THEN SIF.dbo.oitw.IsCommited END),0) AS '10Alloc',
                         isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '20' THEN SIF.dbo.oitw.IsCommited END),0) AS '20Alloc',
                         isnull(SUM(CASE WHEN SIF.dbo.oitw.whscode = '30' THEN SIF.dbo.oitw.IsCommited END),0) AS '30Alloc',
                         isnull(MAX(binlocat2.[10SD]),0) AS '10SD'
                        FROM         SIF.dbo.OITW LEFT OUTER JOIN (
                             SELECT      Rbeacon.dbo.binlocat.PRODUCT COLLATE SQL_Latin1_General_CP850_CI_AS as Product,
                                         SUM(CASE WHEN Rbeacon.dbo.binlocat.BINLABEL = '10SD'OR Rbeacon.dbo.binlocat.BINLABEL LIKE '10WA%' THEN Rbeacon.dbo.binlocat.QUANTITY ELSE 0 END) AS [10SD]
                                    FROM Rbeacon.dbo.BINLOCAT
                             GROUP BY Rbeacon.dbo.binlocat.PRODUCT
                        ) AS BINLOCAT2 ON SIF.dbo.OITW.ItemCode = BINLOCAT2.PRODUCT
   GROUP BY SIF.dbo.OITW.ItemCode
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.