How to use a subselect in a LEFT JOIN ON clause?

Question

I have a table t with

ORD_DATE	ORD_ID	ORD_REF	ORD_TYPE1	ORD_TYPE2	PRODNUM	PRODQUAL	PRICE
2020-09-01	101	101	ORDER	ORDER	456	F	555
2020-09-02	102	101	CONF	ORDER	456	F	555
2020-11-30	103	102	ORDER	ORDER	123	K	444
2020-12-01	104	102	CONF	ORDER	123	K	444
2020-12-01	105	103	ORDER	ORDER	123	K	444
2020-12-01	106	104	ORDER	ORDER	123	K	333
2020-12-02	107	104	CONF	ORDER	123	K	333
2020-12-08	108	104	CONF	RETURN	123	K	-333
2020-12-01	109	105	ORDER	ORDER	123	F	222
2020-12-02	110	105	CONF	ORDER	123	F	222

and a table s with:

ORD_DATE	PROD_NUMBER	PROD_QUAL
2020-12-01-00.00.00.000000	123	K
2020-12-01-00.00.00.000000	123	L

• In table t are all sales per day.
• A sale has 2 stages: first the order is generated when the customer buys something ("ORDER"/"ORDER"). Then it gets confirmed which is at the next day or within the next days normally ("CONF"/"ORDER"). If a customer sends the product back it's a return ("CONF"/"RETURN").

In table s are the products that are "second hand".

• if a product is in that table it means all sales from table t with

ORDER_TYPE_1 = "ORDER"
AND ORDER_TYPE_2 = "ORDER"
AND t.ORD_DATE >= s.ORD_DATE
AND t.PROD_NUMBER = s.PROD_NUMBER
AND t.PROD_QUAL = s.PROD_QUAL

count as "second hand".

I need the sum of all "second hand" sales that are confirmed from the year 2021 and month 12. But only rows with CONF/ORDER or CONF/RETURN should be in the calculation. I have CAL_YEAR and CAL_MONTH in table t for that (omitted for less clutter).

From table t only ORDER_REF 105 matches that and the sum would be 0 because only these 2 rows matter:

| 2020-12-02 | 107    | 104     | CONF      | ORDER     | 123     | K        | 333
| 2020-12-08 | 108    | 104     | CONF      | RETURN    | 123     | K        | -333

My code so far:

SELECT SUM(PRICE)
FROM t
--
LEFT JOIN s
ON t.PRODNUM = s.PRODNUM
AND t.PRODQUAL = s.PRODQUAL
AND (SELECT ORD_DATE FROM t WHERE ORDER_TYPE_1 = 'ORDER' AND ORDER_TYPE_2 = 'ORDER') >= s.ORD_DATE
--
WHERE CAL_YEAR = 2021
AND CAL_MONTH = 12
AND ORDER_TYPE_1 = 'CONF'
AND ORDER_TYPE_2 IN ('ORDER', 'RETURN')
--
GROUP BY PRICE
;

SQL-Error: "single-row subquery returns more than one row

My problem is limiting the LEFT JOIN to ORDER/ORDER (so that ORDER_REF 105 is in) but only use CONF/ORDER and CONF/RETURN for the sum (so that ORDER_REF 102 is out).

Anyone can help?

Answer 1

The simplest way I can think of would be to do a self-join, where you join a second copy of table t aliased t2 to use for the CONF/ORDER and CONF/RETURN rows, while you use t for the ORDER/ORDER rows.

SELECT SUM(t2.PRICE)
FROM t
--
INNER JOIN t t2
ON t2.ORD_REF = t.ORD_REF
AND t2.ORDER_TYPE_1 = 'CONF'
AND t2.ORDER_TYPE_2 IN ('ORDER', 'RETURN')
-- 
LEFT JOIN s
ON t.PRODNUM = s.PRODNUM
AND t.PRODQUAL = s.PRODQUAL
AND t.ORD_DATE >= s.ORD_DATE
--
WHERE t.CAL_YEAR = 2021
AND t.CAL_MONTH = 12
AND t.ORDER_TYPE_1 = 'ORDER'
AND t.ORDER_TYPE_2 = 'ORDER'
;

If you need it to be more efficient, you could use analytic/window functions to pull the summed price from the CONF rows into the ORDER/ORDER row as a new column. This way it will only query table t once instead of twice.

SELECT SUM(t2.order_price_sum)
FROM (select t.*, 
            sum(case when ORDER_TYPE_1 = 'CONF' 
                          AND ORDER_TYPE_2 IN ('ORDER', 'RETURN') 
                     then t.price 
                     else 0 end) over (partition by ord_ref) as order_price_sum
            from t) t2
--
LEFT JOIN s
ON t2.PRODNUM = s.PRODNUM
AND t2.PRODQUAL = s.PRODQUAL
AND t2.ord_date >= s.ORD_DATE
--
WHERE CAL_YEAR = 2021
AND CAL_MONTH = 12
AND ORDER_TYPE_1 = 'ORDER'
AND ORDER_TYPE_2 = 'ORDER'
;