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?
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.