Is there any way to avoid multiple joins in one query-CodePudding

I have 2 tables. Product table contain my skus which has these columns: id, name, sku, image

my other table is my warehouse_in_and_out which has: id, date_time, sku_id, inbound_outbound, adjustment. date_time and sku_id are unique together so I have only 1 row for each sku each day. Also my inbound_outbound and adjustment is an array of integers.

I want to get inbound_outbound for each day in a date range for each sku and also want to have sum of all inbound_outbound for all rows of each sku

I have came up with something like this:

SELECT P.ID ,SKU,
    MAX(CASE
    WHEN DATE_TIME = '12/31/2021' THEN WARE.INBOUND_OUTBOUND
        ELSE NULL
    END) AS INBOUND_OUTBOUND_1,
    MAX(CASE
    WHEN DATE_TIME = '12/31/2021' THEN WARE.ADJUSTMENT
        ELSE NULL
    END) AS ADJUSTMENT_1,
    MAX(CASE
    WHEN DATE_TIME = '12/30/2021' THEN WARE.INBOUND_OUTBOUND
        ELSE NULL
    END) AS INBOUND_OUTBOUND_2,
    MAX(CASE
    WHEN DATE_TIME = '12/30/2021' THEN WARE.ADJUSTMENT
        ELSE NULL
    END) AS ADJUSTMENT_2
FROM PRODUCTS AS P
LEFT JOIN WAREHOUSE_IN_AND_OUT AS WARE ON WARE.SKU_ID = P.ID
WHERE P.STORE_ID IN ('1',
        '2',
        '3',
        '4',
        '5')
GROUP BY P.ID, SKU
ORDER BY SKU

My result in json is:

{
    id: '1',
    sku: 'ABC',
    inbound_outbound_1: null,
    adjustment_1: null,
    inbound_outbound_2: [
      4960, 4960, 4960, 4960, 4960, 4960,
      4960, 4960, 4960, 4960, 4960, 4960,
      4960, 4960, 4960, 4960, 4960, 4960,
      4960, 4960, 4960, 4960, 4960, 4960,
      4960, 4960, 4960, 4960, 4960, 4960,
      4960, 4960, 4960, 4960, 4960, 4960,
      5000, 4960
    ],
    adjustment_2: null
  }

when I want to add my total to this sql I get 2 same rows:

SELECT P.ID ,SKU,
    MAX(CASE
    WHEN DATE_TIME = '12/31/2021' THEN WARE.INBOUND_OUTBOUND
        ELSE NULL
    END) AS INBOUND_OUTBOUND_1,
    MAX(CASE
    WHEN DATE_TIME = '12/31/2021' THEN WARE.ADJUSTMENT
        ELSE NULL
    END) AS ADJUSTMENT_1,
    MAX(CASE
    WHEN DATE_TIME = '12/30/2021' THEN WARE.INBOUND_OUTBOUND
        ELSE NULL
    END) AS INBOUND_OUTBOUND_2,
    MAX(CASE
    WHEN DATE_TIME = '12/30/2021' THEN WARE.ADJUSTMENT
        ELSE NULL
    END) AS ADJUSTMENT_2,
    COALESCE(
                    (SELECT SUM(I)
                        FROM UNNEST(WARE.INBOUND_OUTBOUND) AS I),0)::INTEGER AS TOTAL
FROM PRODUCTS AS P
LEFT JOIN WAREHOUSE_IN_AND_OUT AS WARE ON WARE.SKU_ID = P.ID
WHERE P.STORE_ID IN ('1',
        '2',
        '3',
        '4',
        '5')
GROUP BY P.ID, SKU, INBOUND_OUTBOUND
ORDER BY SKU

my result is:

[
  {
    id: '1',
    sku: 'ABC',
    total: -4,
    inbound_outbound_1: [ 52, -56 ],
    adjustment_1: null,
    inbound_outbound_2: null,
    adjustment_2: null
  },
  {
    id: '1',
    sku: 'ABC',
    total: 188520,
    inbound_outbound_1: null,
    adjustment_1: null,
    inbound_outbound_2: [
      4960, 4960, 4960, 4960, 4960, 4960,
      4960, 4960, 4960, 4960, 4960, 4960,
      4960, 4960, 4960, 4960, 4960, 4960,
      4960, 4960, 4960, 4960, 4960, 4960,
      4960, 4960, 4960, 4960, 4960, 4960,
      4960, 4960, 4960, 4960, 4960, 4960,
      5000, 4960
    ],
    adjustment_2: null
  }
]

but I want to get only one row for each sku. my expected result is:

[
  {
    id: '1',
    sku: 'ABC',
    total: 188516,
    inbound_outbound_1:  [ 52, -56 ],
    adjustment_1: null,
    inbound_outbound_2: [
      4960, 4960, 4960, 4960, 4960, 4960,
      4960, 4960, 4960, 4960, 4960, 4960,
      4960, 4960, 4960, 4960, 4960, 4960,
      4960, 4960, 4960, 4960, 4960, 4960,
      4960, 4960, 4960, 4960, 4960, 4960,
      4960, 4960, 4960, 4960, 4960, 4960,
      5000, 4960
    ],
    adjustment_2: null
  }
]

Thanks to Jon I have implemented my schema inside dbfiddle

I want to have 6 and 8 result in one result if thats possible. I don't want to have many results for each sku like in 7 parag. I want to have total for each sku

CodePudding user response：

I would use a lateral join rather than a sub-query in your SELECT...

SELECT P.ID, P.SKU,
    MAX(CASE
    WHEN DATE_TIME = '12/31/2021' THEN WARE.INBOUND_OUTBOUND
        ELSE NULL
    END) AS INBOUND_OUTBOUND_1,
    MAX(CASE
    WHEN DATE_TIME = '12/31/2021' THEN WARE.ADJUSTMENT
        ELSE NULL
    END) AS ADJUSTMENT_1,
    MAX(CASE
    WHEN DATE_TIME = '12/30/2021' THEN WARE.INBOUND_OUTBOUND
        ELSE NULL
    END) AS INBOUND_OUTBOUND_2,
    MAX(CASE
    WHEN DATE_TIME = '12/30/2021' THEN WARE.ADJUSTMENT
        ELSE NULL
    END) AS ADJUSTMENT_2,
       MAX(CASE
    WHEN DATE_TIME = '12/29/2021' THEN WARE.INBOUND_OUTBOUND
        ELSE NULL
    END) AS INBOUND_OUTBOUND_3,
    MAX(CASE
    WHEN DATE_TIME = '12/29/2021' THEN WARE.ADJUSTMENT
        ELSE NULL
    END) AS ADJUSTMENT_3,
    COALESCE(SUM(I.SUM_I),0)::INTEGER AS TOTAL
FROM PRODUCTS AS P
LEFT JOIN WAREHOUSE_IN_AND_OUT AS WARE ON WARE.SKU_ID = P.ID
CROSS JOIN LATERAL (SELECT SUM(I) SUM_I FROM UNNEST(WARE.INBOUND_OUTBOUND) I) I
GROUP BY P.ID, SKU
ORDER BY SKU

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=9f94ecd3f1c38ab01eca06699c980424

CodePudding user response：

While @MatBailie has provided a very nice solution, I thought I'd add an example of a direct / simple combination of your two statements. This can be helpful in lots of other cases as well.

The fiddle, with both solutions

WITH cte1 AS (
         SELECT P.ID, P.SKU
              , MAX(CASE
                      WHEN DATE_TIME = '12/31/2021' THEN WARE.INBOUND_OUTBOUND
                          ELSE NULL
                      END
                   ) AS INBOUND_OUTBOUND_1
              , MAX(CASE
                      WHEN DATE_TIME = '12/31/2021' THEN WARE.ADJUSTMENT
                          ELSE NULL
                      END
                   ) AS ADJUSTMENT_1
              , MAX(CASE
                      WHEN DATE_TIME = '12/30/2021' THEN WARE.INBOUND_OUTBOUND
                          ELSE NULL
                      END
                   ) AS INBOUND_OUTBOUND_2
              , MAX(CASE
                     WHEN DATE_TIME = '12/30/2021' THEN WARE.ADJUSTMENT
                          ELSE NULL
                      END
                   ) AS ADJUSTMENT_2
              , MAX(CASE
                      WHEN DATE_TIME = '12/29/2021' THEN WARE.INBOUND_OUTBOUND
                          ELSE NULL
                      END
                   ) AS INBOUND_OUTBOUND_3
              , MAX(CASE
                     WHEN DATE_TIME = '12/29/2021' THEN WARE.ADJUSTMENT
                          ELSE NULL
                      END
                   ) AS ADJUSTMENT_3
           FROM PRODUCTS AS P
           LEFT JOIN WAREHOUSE_IN_AND_OUT AS WARE ON WARE.SKU_ID = P.ID
          GROUP BY P.ID, SKU
          ORDER BY SKU
     )
   , cte2 AS (
         SELECT sku, SUM(total) as total
           FROM (
                  SELECT P.SKU
                       , COALESCE(
                                    (SELECT SUM(I)
                                       FROM UNNEST(WARE.INBOUND_OUTBOUND) AS I
                                    )
                                    , 0
                                 )::INTEGER AS TOTAL
                    FROM PRODUCTS AS P
                    LEFT JOIN WAREHOUSE_IN_AND_OUT AS WARE ON WARE.SKU_ID = P.ID
                   ORDER BY SKU
                ) AS test
          GROUP BY SKU
     )
SELECT cte1.*, cte2.total
  FROM cte1
  JOIN cte2 
    ON cte1.SKU = cte2.SKU
;