CODEWITHSUNDEEP
sqlpostgresqljoincase
Visahan
Visahan

Reputation: 1192

SQL Case expression in Join clause

I have 2 tables as follows

table1

id    |      date
------+---------------------
 1    |     1/12/2017
 1    |     3/12/2017
 1    |     10/2/2018     
 1    |     10/4/2018
 2    |     1/7/2018
 2    |     12/9/2018 
 2    |     13/9/2018
 2    |     1/10/2018

table2

id    |      date1     |      date2   |  value
------+----------------+--------------+----------
 1    |     1/1/2018   |    1/1/2018  |    1
 1    |     15/2/2018  |    1/1/2018  |    4    
 1    |     10/4/2018  |    15/2/2018 |    7
 2    |     1/7/2018   |    1/7/2018  |    5
 2    |     13/9/2018  |    1/7/2018  |    2
 2    |     1/10/2018  |    13/9/2018 |    14

I want to add the value column to table 1, matching by id, and if the date is between date1 and date 2. However, in the case of date1 = date2, the condition should be the date being on or before date1

The results should be 

id    |      date       |  value
------+-----------------+---
 1    |     1/12/2017   |    1
 1    |     3/12/2017   |    1
 1    |     10/2/2018   |    4    
 1    |     10/4/2018   |    7
 2    |     1/7/2018    |    5
 2    |     12/9/2018   |    2
 2    |     13/9/2018   |    2
 2    |     1/10/2018   |    14

I tried the following SQL query

SELECT table1.id, table1.date, table2.value
  FROM table1 
  LEFT JOIN table2 ON (table1.id = table2.id AND 
  CASE 
    WHEN table2.date1 = table2.date2 THEN table1.date <= table2.date
    ELSE table1.date BETWEEN table2.date2 AND table2.date1 );

AND

SELECT table1.id, table1.date, table2.value
  FROM table1 
  LEFT JOIN table2 ON (table1.id = table2.id) WHERE 
  CASE 
    WHEN table2.date1 = table2.date2 THEN table1.date <= table2.date
    ELSE table1.date BETWEEN table2.date2 AND table2.date1;

however this does not give the result I need. Where am I making the mistake

Upvotes: 1

Views: 194

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

You don't need a case for this. Just boolean conditions and these can all go in the ON clause:

SELECT t1.id, t1.date, t2.value
FROM table1 t1 JOIN
     table2 t2
     ON t1.id = t2.id AND
        ((t2.date1 < t2.date2 AND
          t1.date BETWEEN t2.date1 AND t2.date2)
         ) OR
         (t2.date1 = t2.date2 AND
          t1.date <= t2.date1
         ) 
        );

You can also simplify this. One method is:

     ON t1.id = t2.id AND
        t1.date <= date2 AND
        (t2.date1 = t2.date2 OR
         t1.date >= t2.date1
        );

Upvotes: 1

Hooman Nemati
Hooman Nemati

Reputation: 97

try this:

SELECT table1.id, table1.date, table2.value
  FROM table1 
  JOIN table2 ON table1.id = table2.id
where  (table2.date1=table2.date2 and table1.date<=table2.date1)
or ( table1.date BETWEEN table2.date2 AND table2.date1 );

Upvotes: 0

mkRabbani
mkRabbani

Reputation: 16908

Try this way-

UPDATE A
SET A.Value = B.Value
FROM TABLE1 A 
INNER JOIN TABLE2 B
ON A.ID = B.ID AND A.Date BETWEEN B.Date1 AND B.DATE2

Then try with this-

UPDATE A
SET A.Value = B.Value
FROM TABLE1 A 
INNER JOIN TABLE2 B
ON A.ID = B.ID AND (A.Date >= B.Date1 AND A.Date <= B.DATE2)

Upvotes: 0

Related Questions