Aliy Aliy - 23 days ago 9
SQL Question

Get rows from table of last 5 days in IBM DB2 by joining another table

I have two tables in DB2.

table1
----
sidpk pid
--------------------------
24gsr3 xyz
67sfr9 xyz
67sts8 xyz

table2
-------------------------
sid date
--------------------------
24gsr3 2017-09-24
67sfr9 2017-09-23
67sts8 2017-09-15
.
.
and so on


I'm trying to fetch all rows that are available that were created 5 days before from today from table2.

In the above case, I expect it should return be 24gsr3, 67sfr9

What is missing in my query?

Please find it.

SELECT a.sidpk from table1 a
LEFT JOIN
table2 b
ON a.sidpk= b.sid
WHERE a.pid='xyz' AND b.date>= DATE_SUB(CURDATE(), INTERVAL 5 DAY) AND
b.date<= CURDATE()
ORDER BY b.date DESC

Answer Source

This should work

SELECT a.sidpk from table1 a
  LEFT JOIN table2 b
    ON a.sidpk= b.sid
 WHERE a.pid='xyz' 
   AND b.date >= current date - 5 days
   AND b.date <= current date
 ORDER BY b.date DESC