Join tables on varying condition for each row-CodePudding

So I have two tables:

CREATE TABLE table1
    (`PRIMARY_DT` date, `CUST_ID` int)
;
    
INSERT INTO table1
    (`PRIMARY_DT`, `CUST_ID`)
VALUES
    ('2012-03-02', 878 ),
    ('2012-07-02', 456 ),
    ('2012-09-02', 789 )
;


CREATE TABLE table2
    (`dt` date, `CUST_ID` int, `value` int)
;
    
INSERT INTO table2
    (`dt`, `CUST_ID`, `value`)
VALUES
    ('2012-03-8', 878, 1)
;

I need to add the value from table2 to table1 if a condition is met (date in table 2 is within 7 days of date in table 1). If that isn't the case, then just add value of -9999. So something like this:

PRIMARY_DT	CUST_ID	value
'2012-03-02'	878	1
'2012-03-02'	456	-9999
'2012-03-02'	789	-9999

So far, I can join if the condition is met with the following code:

SELECT t1.PRIMARY_DT,
      t1.CUST_ID,
      t2.value
FROM table1 t1
JOIN table2 t2 ON t1.CUST_ID = t2.CUST_ID
WHERE t2.value =  (SELECT MIN(tt2.value)
                    FROM table2 tt2
                    WHERE tt2.dt BETWEEN t1.PRIMARY_DT AND t1.PRIMARY_DT 7)

I tried adding the -9999 for the cases where there is no match but I'm having tunnel vision. The below gives the same result as above:

SELECT t1.PRIMARY_DT,
      t1.CUST_ID,
      t2.value
FROM table1 t1
JOIN table2 t2 ON t1.CUST_ID = t2.CUST_ID
WHERE t2.value = (CASE WHEN
                   (SELECT MIN(tt2.value)
                    FROM table2 tt2
                    WHERE tt2.dt BETWEEN t1.PRIMARY_DT AND t1.PRIMARY_DT 7) IS NULL THEN
                   -9999 ELSE (SELECT MIN(tt2.value)
                    FROM table2 tt2
                    WHERE tt2.dt BETWEEN t1.PRIMARY_DT AND t1.PRIMARY_DT 7) END)

Either query only gives me:

PRIMARY_DT	CUST_ID	value
'2012-03-02'	878	1

CodePudding user response：

You can largely simplify your solution by

using a LEFT JOIN between the two tables
apply the date condition inside one CASE statement

SELECT t1.*, 
       CASE WHEN DATEDIFF(t2.dt, t1.PRIMARY_DT) < 7 
            THEN t2.value 
            ELSE -9999 
       END AS value
FROM      table1 t1
LEFT JOIN table2 t2
       ON t1.CUST_ID = t2.CUST_ID

Check the demo here.

CodePudding user response：

So my tunnel vision is gone after posting this, here is the answer:

SELECT t1.PRIMARY_DT,
       t1.CUST_ID,
       CASE WHEN (SELECT MIN(tt2.value)
                    FROM table2 tt2
                    WHERE tt2.dt BETWEEN t1.PRIMARY_DT AND t1.PRIMARY_DT 7) IS NULL
            THEN 0 ELSE (SELECT MIN(tt2.value)
                    FROM table2 tt2
                    WHERE tt2.dt BETWEEN t1.PRIMARY_DT AND t1.PRIMARY_DT 7) END
FROM table1 t1
LEFT OUTER JOIN table2 t2 ON t1.CUST_ID = t2.CUST_ID