Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – selecting after a date and comparing between including null

Requirement: To select entries relating to an account where delivered after date x, returned between dates y and z and also where returned is null.

I’ve attempted to use the following and it selects everything delivered before the stipulated date and then returned only gives null.

"SELECT * 
    FROM charges 
    WHERE ship_to = '$shipper' 
    AND delivered <= '$end' 
    AND ((returned BETWEEN '$start' AND '$end') OR (returned IS NULL))";

Where for example $start = ‘2024-03-01’ and $end = ‘2024-03-31’, it is returning:

barcode delivered returned
28366 2024-02-15 NULL
27433 2024-03-12 NULL

Where I would expect

barcode delivered returned
28366 2024-02-15 NULL
27433 2024-03-12 NULL
22246 2024-02-15 2024-03-10
28843 2024-03-12 2024-03-21

What would be the best syntax to use to achieve this?

Tags:

2

Answers


  1. 0

    You have conflicting filters in your WHERE clause.
    ((returned BETWEEN '$start' AND '$end') AND (returned IS NULL))
    returned cannot both be between start and end and null. Try an OR

    "SELECT * 
FROM charges 
WHERE ship_to = '$shipper' 
AND delivered <= '$end' 
AND ((returned BETWEEN '$start' AND '$end') OR (returned IS NULL))";
    Login or Signup to reply.
  2. 0 
    SELECT * FROM charges 
    WHERE ship_to = '$shipper' 
    AND delivered <= '$end' 
    AND ((returned BETWEEN '$start' AND '$end') OR (returned IS NULL))

Use OR instead of AND
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search