CODEWITHSUNDEEP
mysqlwhere-clause
StuBlackett
StuBlackett

Reputation: 3855

Select Between 2 dates but also select NULL values

I'm trying to run a query where I select dates between 2 values

OR ( `order_products`.`deleted_at` BETWEEN
                 '2021-01-01 00:00:00' AND '2021-07-28 23:59:59' )

But I also need to collect order_products with NULL values.

Is this at all possible, as when I put an

OR `order_products`.`deleted_at` IS NULL

Into my code, It seems to just return absolutley everything in the database :-(

My current code, for context is :

SELECT 
`orders`.`id`, 
`orders`.`reference`, 
`orders`.`order_date`, 
`orders`.`cancellation_date`,
 `order_products`.`description`, 
`order_products`.`st_code`, 
`order_products`.`supplier_stock_code`,
 `order_products`.`status`, 
`order_products`.`deleted_at`,
 `supplier`.`name`, 
 order_invoice.created_at AS credit_date, 
fitters.account_number AS fitter_account_number, 
fitters.name AS fitter 
FROM `order_products`
 LEFT JOIN `orders` ON (`orders`.`id` = `order_products`.`order_id`) 
LEFT JOIN `supplier` ON (`order_products`.`supplier_id` = `supplier`.`id`) 
LEFT JOIN `fitters` ON (`orders`.`fitter_id` = `fitters`.`id`) 
JOIN `order_invoice` ON (`order_invoice`.`order_id` = `orders`.`id`) 
WHERE `cancellation_date` IS null 
AND `cancellation_date` BETWEEN '2021-01-01 00:00:00' AND '2021-07-28 23:59:59'
 AND `order_invoice`.`status` = 'approved' 
OR (`order_products`.`deleted_at` BETWEEN '2021-01-01 00:00:00' 
OR `order_products`.`deleted_at` IS NULL
AND '2021-07-28 23:59:59') 
AND `order_products`.`product_type` = 'product' 
ORDER BY `orders`.`id` ASC

Upvotes: 0

Views: 40

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522797

Your syntax is off. You need:

WHERE order_products.deleted_at >= '2021-01-01' AND
      order_products.deleted_at <  '2021-07-29' OR
      order_products.deleted_at IS NULL

Note that I wrote the datetime check using date inequalities, which seems cleaner to me. Also, we don't need any extra parentheses here as AND has higher precedence than OR.

Upvotes: 1

Rinkal Rohara
Rinkal Rohara

Reputation: 352

Please try changing the where condition to WHERE cancellation_date IS null AND cancellation_date BETWEEN '2021-01-01 00:00:00' AND '2021-07-28 23:59:59' AND order_invoice.status = 'approved' OR (order_products.deleted_at IS NULL OR order_products.deleted_at BETWEEN '2021-01-01 00:00:00' AND '2021-07-28 23:59:59') AND order_products.product_type = 'product'

Upvotes: 1

Related Questions