I have below two tables:
~ What I am looking to do: I want to append the Price from Table 2 (t2) to Table 1 (t1), by joining on Quantity and YYYY_MM. Each t2.price was active in a certain time range (t2.Price_Active_Date_From and t2.Price_Active_Date_To), and the t1.Order_Date should fall within this range. When there is no active price at that order date, I want the result to return null.
So the result should look like:
What I have tried below so far, which works to get the Price_Active_At_Order when there is a price at a certain date, but it doesn't work when there's no active price. How to add a condition in the join so this works?:
select distinct
t1.Product_NR,
t1.Customer,
t1.Quantity,
t2.Price as Price_Active_At_Order,
t1.Order_YYYYMM as Order_Date
from Table_1 t1
join Table_2 t2 on t1.Product_NR = t2.Product_NR
and t1.Quantity = t2.Quantity
and t1.Order_YYYYMM between t2.Price_Active_Date_From and t2.Price_Active_Date_To
CodePudding user response：
Inner Join returns empty if the matching results are not found. You can handle it on code level for setting null value if empty response has been returned. However in SQL you'll only get empty result.
If you still want to get NULL values you can use LEFT Join that will return the values for the rows not match with the condition as null for the table on the right side of the Join.
Similarly for Right Join.
When using Left Join, Right Join, Full (Outer) Join, it is possible to return NULL value while (inner) join, cross join will not return NULL value.
For understanding purposes you can go through this link for SQL Join: https://www.geeksforgeeks.org/sql-join-set-1-inner-left-right-and-full-joins/
And answer of the below question: https://docs.microsoft.com/en-us/answers/questions/99875/sql-query-full-join-multiple-tables-but-return-nul.html
CodePudding user response：
Using the data from your question the result is a bit different because the order date from tbl_1 for product 10334 and tbl_1 quantity=1 is out of the period in tbl_2, so the resulting price is Null.
WITH
tbl_1 AS
(
Select 10023 "PRODUCT_NR", 'X' "CUSTOMER", 20 "QUANTITY", 202004 "ORDER_DATE" From Dual UNION ALL
Select 10023 "PRODUCT_NR", 'Y' "CUSTOMER", 10 "QUANTITY", 202203 "ORDER_DATE" From Dual UNION ALL
Select 10334 "PRODUCT_NR", 'X' "CUSTOMER", 1 "QUANTITY", 202204 "ORDER_DATE" From Dual
),
tbl_2 AS
(
Select 10023 "PRODUCT_NR", 1 "QUANTITY", 100 "PRICE", 202101 "DATE_FROM", 999912 "DATE_TO" From Dual UNION ALL
Select 10023 "PRODUCT_NR", 10 "QUANTITY", 120 "PRICE", 202101 "DATE_FROM", 999912 "DATE_TO" From Dual UNION ALL
Select 10023 "PRODUCT_NR", 20 "QUANTITY", 300 "PRICE", 201605 "DATE_FROM", 202001 "DATE_TO" From Dual UNION ALL
Select 10023 "PRODUCT_NR", 20 "QUANTITY", 250 "PRICE", 202101 "DATE_FROM", 999912 "DATE_TO" From Dual UNION ALL
Select 10023 "PRODUCT_NR", 100 "QUANTITY", 400 "PRICE", 202101 "DATE_FROM", 999912 "DATE_TO" From Dual UNION ALL
Select 10334 "PRODUCT_NR", 1 "QUANTITY", 25 "PRICE", 202101 "DATE_FROM", 202111 "DATE_TO" From Dual UNION ALL
Select 10334 "PRODUCT_NR", 1 "QUANTITY", 30 "PRICE", 202101 "DATE_FROM", 202111 "DATE_TO" From Dual
)
SELECT
t1.PRODUCT_NR "PRODUCT_NR",
t1.CUSTOMER "CUSTOMER",
t1.QUANTITY "QUANTITY",
t2.PRICE "PRICE_AT_ORDER",
t1.ORDER_DATE "ORDER_DATE"
FROM
tbl_1 t1
LEFT JOIN
tbl_2 t2 ON(t2.PRODUCT_NR = t1.PRODUCT_NR And t2.QUANTITY = t1.QUANTITY And t1.ORDER_DATE Between t2.DATE_FROM And DATE_TO)
ORDER BY
t1.PRODUCT_NR,
t1.ORDER_DATE
--
-- R e s u l t
--
-- PRODUCT_NR CUSTOMER QUANTITY PRICE_AT_ORDER ORDER_DATE
-- ---------- -------- ---------- -------------- ----------
-- 10023 X 20 Null 202004
-- 10023 Y 10 120 202203
-- 10334 X 1 Null 202204
As you can see, all that should be done is change your JOIN into LEFT JOIN. Join (Inner Join) returns no rows when condition has no match. Regards...