Skip to content
Advertisement

Select from 2 tables with conditions

I need a SQL query, for multiple condition select from 2 tables.

table1
order_row | timestamp |
-----------------------
0001      |2016-11-04 |
0002      |2016-11-04 |
0003      |2016-11-04 |
0004      |2016-11-03 |
0006      |2016-11-03 |

table2
order_row | timestamp |
-----------------------
0001      |2016-11-05 |
0002      |2016-11-04 |
0003      |2016-11-04 |
0004      |2016-11-04 |
0005      |2016-11-04 |
0006      |2016-11-02 |

I want to get all rows so that I get all order_row rows from table2 which are not present in table1 and order_row rows from table2 whose timestamp is newer in table2 than table1. And checks only rows from table 2 where timestamp is newer than 2016-11-03.
Result must be:

order_row | 
----------
0001      | because timestamp is newer in table2
0004      | because timestamp is newer in table2
0005      | because it's not present in table1

Advertisement

Answer

This should do it:

SELECT t2.*
FROM Table2 AS t2
LEFT JOIN Table1 AS t1 
   ON t2.order_row = t1.order_row 
WHERE t1.order_row IS NULL OR t2.`timestamp` > t1.`timestamp` 

Demo here

Edit: If you want only records from table2 newer than '2016-11-03' to be considered, then simply add:

t2.`timestamp` > '2016-11-03' AND ( ... other conditions here ...)
9 People found this is helpful
Advertisement