Question 113126 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Join tables on varying condition for each row

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
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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

  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search