SQL's not between dates

I was baffled by a problem about selecting rows which is not between given dates. Technically the problem was that to select layby orders where users not able to pay for the past 14 days. Below is the first sql I made.

"SELECT
       *
  FROM
       orders t0
  LEFT JOIN order_items t1 ON t0.id = t1.order_id
  LEFT JOIN payments t2 ON t0.id = t2.payments
  WHERE t2.date_paid NOT BETWEEN '2012-05-12' AND '2012-05-26'
"
This query resulted to joining the order table and order items table but not the payments table which fields were null.

So I try another with slight change on the WHERE clause. Since joining the payments did not result to my desired outcome I place the payments on WHERE clause and see if this get the desired result.

"SELECT
       *
  FROM
       orders t0
  LEFT JOIN order_items t1 ON t0.id = t1.order_id
  WHERE
     ( SELECT count(*) FROM payments t2 WHERE t2.date_paid BETWEEN '2012-05-12' AND '2012-05-26' ) <= 0"

This results to my desired output but I am not confident on this one.



Comments

Popular Posts