CODEWITHSUNDEEP
mysqldatenull
Seybsen
Seybsen

Reputation: 15582

MySQL: get row with date NULL or if present get the row with a set date

I got a table for some product prices where I can set one price which is always valid if there is no other price which is valid at the moment the query runs.

id | item_id | valid_from | valid_to   | price
---+---------+------------+------------+------
1  | 1337    | 2013-01-17 | 2013-01-18 | 35.50
2  | 1337    | NULL       | NULL       | 39.95
3  | 1337    | 2013-01-13 | 2013-01-18 | 36.00

I can't figure out how to get the correct price for NOW() (today that would be 35.50) and need some help here. If there are two or more prices which are valid for a time I expect to get the one which started later. So far I got:

SELECT price
FROM my_table
WHERE item_id = 1337
AND (
    valid_from <= NOW() AND
    valid_to >= NOW()
) OR (
    id NOT IN (
        SELECT id
        FROM my_table
        WHERE item_id = 1337
        AND valid_from <= NOW()
        AND valid_to >= NOW()
    ) AND
    valid_from IS NULL AND
    valid_to IS NULL
)
ORDER BY valid_from DESC
LIMIT 1

btw I'm not really happy with this tablestructure. If you have additionally comments on it I would like to read them. Thank you very much.

Upvotes: 0

Views: 117

Answers (2)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this, I think checking valid_from for NULL is enough.

SELECT item_id, price
FROM tbl
WHERE item_id = 1337
     AND  valid_from <= NOW()
     AND  valid_to >= NOW()
     OR (valid_from IS NULL AND item_id = 1337) 
ORDER BY CASE WHEN valid_from IS NULL 1 ELSE 0 END, valid_from
LIMIT 1

Upvotes: 0

D Mac
D Mac

Reputation: 3809

Instead of making the date values for the default price be NULL, you could make them very small and big: make valid_from = Jan 1, 1970 and valid_to = Jan 1, 2038. Then those records will always be included, but won't be the only ones if there are other superseding prices.

The issue becomes how to sort the result set, but this depends on your business rules. Right now you're sorting by valid_from, and you could continue to do that. If you will want the lowest price, and the default price will always be highest, you can sort by price. Otherwise, you could sort by length of time between NOW() and the price date boundaries and the closest dates win. As I say, that depends on how you want to choose among several competing possible prices.

Upvotes: 1

Related Questions