SQL check value from another table

Question

I've got multiple tables I made my query like this :

SELECT a.creation, b.caseno, c.instanceno
FROM TableB b
JOIN TableA a
ON a.caseno = b.caseno
JOIN TableC c
ON c.caseno = b.caseno
WHERE a.creation BETWEEN '2021-01-01' AND '2021-12-31'

I've got TableD who contains the following column

| InstanceNo | Position | Creation | TaskNo |

The idea is to add a new colum (result) on my query. If instance from c.instanceno exist on tableD and taskno is 30 or 20, in that case i would like the d.creation but for the max(position). If not the value null is enough for the column result.

Answer 1

SELECT a.creation, b.caseno, c.instanceno, d.creation
FROM TableB b
JOIN TableA a
ON a.caseno = b.caseno
JOIN TableC c
ON c.caseno = b.caseno
LEFT JOIN (SELECT MAX(position) position, instanceno, creation, taskno FROM TableD GROUP BY instanceno, creation, taskno) d
ON d.instanceno = c.instanceno
AND d.taskno in (20,30)
WHERE a.creation BETWEEN '2021-01-01' AND '2021-12-31'