Mysql query question

Question

I have 2 tables:

Customer: customerid - int, pri-key,auto fname - varchar sname -varchar housenum - varchar street -varchar

Items: itemid - int,pri-key,auto type - varchar collectiondate - date releasedate - date customerid - int

EDIT I need a query which will get me all items that have a releasedate of 3 days into the future, including today

i.e The query should return customerid,fname,sname,street,housenum,type,releasedate for all items which have releasedate of today or 3 days into the future.e.g, if todays date was 2010-12-24, and an item has release date of 2010-12-25 then it would be returned ion the query.

The idea is that if an item is due for release (today or 3 days ahead of today) then it will show this. thanks in advance

Answer 1

SELECT  c.customerid, c.fname, c.sname, c.street, c.housenum, i.type, i.releasedate
FROM    items i
JOIN    customers c
ON      c.customerid = i.customerid
WHERE   i.releasedate >= CURDATE()
        AND i.releasedate < CURDATE() + INTERVAL `4` DAY

Note the use of two comparisons (strict and loose) instead of BETWEEN, and 4 days instead of 3.

This means that if you run this query on Jan 1st, it will return you the items with release date greater or equal than Jan 1st, but strictly less that Jan 5th.

This means that any record with date part of 1, 2, 3, 4 of January will make it to your query.

Answer 2

SELECT c.customerid, c.fname, c.sname, c.street, c.housenum, i.type, i.releasedate 
FROM Items i JOIN Customer c ON i.customerid  = c.customerid 
WHERE i.releasedate BETWEEN NOW() - INTERVAL 3 DAY AND NOW()