Difficulty making 3 JOINS when 2 and 2 joins work just fine-CodePudding

I have a problem. When I do a simple join on 2 tables everything works great. No repetitions. Everything sums up.

      SELECT S.ITEM, SUM(QTY)
      FROM SCPOMGR.SKU S
      JOIN SCPOMGR.PLANARRIV P
      ON S.ITEM=P.ITEM AND P.DEST=S.LOC 
      GROUP BY s.ITEM, p.qty

I do the same again with other 2 tables

enter image description here

  SELECT S.ITEM, SUM(QTY)
      FROM SCPOMGR.SKU S
      JOIN SCPOMGR.RECSHIP R
      ON S.ITEM=R.ITEM AND S.LOC=R.SOURCE
      GROUP BY S.ITEM, R.QTY

And now I try to combine two tables and I start getting those repetitions.

enter image description here

      SELECT S.ITEM AS 'ITEM', SUM(P.QTY) AS 'Forecast Demand', 
      SUM(R.QTY) AS 'Last Week Actual'
      FROM SCPOMGR.SKU S
      JOIN SCPOMGR.PLANARRIV P
      ON S.ITEM=P.ITEM AND P.DEST=S.LOC 
      JOIN SCPOMGR.RECSHIP R
      ON S.ITEM=R.ITEM AND S.LOC=R.SOURCE
      GROUP BY s.ITEM, p.qty, r.qty
      ORDER BY s.ITEM

What can be wrong with 3 joins if 2 and 2 joins work just fine but when I combine all of them I start getting repetitions.

PLANARRIV table doesn't have unique Item and Dest. If someone may know how I can pre-aggregate it and add it to the code

enter image description here

I have 3 tables

SKU - I will eventually only need to use 3 columns from there. Just a simple table of inventory. 1) ITEM number (Unique #) 2) LOC which is a DC# (Not unique, since there are a few DCs only). 3) OHPOST (don't need it for this example, date of inventory).

enter image description here

PLANARRIV - just a forecasting table. QTY shows how much will be needed. Item is not unique here. DEST is also not unique.

enter image description here

RECSHIP - basically shows Actuals, what was shipped. Item not unique. Source is not unique (limited amount of DCs).

enter image description here

CodePudding user response：

Try something like this. Note: I've removed a few terms from your GROUP BY clause, since that looked like a mistake. Feel free to adjust as needed.

My assumption is that you just wanted each SUM per item to be joined together.

If you wanted sums per (item, location) pair, that's different. But we wouldn't place qty in the GROUP BY terms to do that.

WITH cte1 AS (
       SELECT S.ITEM, SUM(QTY) AS sum1
         FROM SCPOMGR.SKU       S
         JOIN SCPOMGR.PLANARRIV P
           ON S.ITEM=P.ITEM
          AND P.DEST=S.LOC 
        GROUP BY s.ITEM
     )
   , cte2 AS (
       SELECT S.ITEM
            , SUM(QTY) AS sum2
         FROM SCPOMGR.SKU     S
         JOIN SCPOMGR.RECSHIP R
           ON S.ITEM=R.ITEM
          AND S.LOC = R.SOURCE
        GROUP BY S.ITEM
     )
SELECT cte1.item, cte1.sum1, cte2.sum2
  FROM cte1
  JOIN cte2
    ON cte1.item = cte2.item
;

CodePudding user response：

Correlated subqueries can work well here:

  SELECT S.ITEM,
         (SELECT SUM(P.QTY)
          FROM SCPOMGR.PLANARRIV P
          WHERE S.ITEM = P.ITEM AND P.DEST = S.LOC
         ),
         (SELECT SUM(P.QTY)
          FROM SCPOMGR.RECSHIP R
          WHERE S.ITEM = R.ITEM AND S.LOC = kR.SOURCE
         ),
  FROM SCPOMGR.SKU S;