[SOLVED] BETWEEN query won't work

[retro]

Apologies for what is probably a basic question - I am quite new to MySQL and still learning!

 

I have a query that filters out records using a calendar input ($DelDate), and determines whether a recurring order is due to be delivered on that date.  This query works fine:

 

SELECT * 
FROM orders
LEFT JOIN customers ON ( customers.id = orders.customer ) 
WHERE (
recur =1
AND DAYOFWEEK( date ) = DAYOFWEEK( '$DelDate' ) 
)
OR (
recur =2
AND DATEDIFF( date, '$DelDate') %14 =0
)
AND date <= '$DelDate'

 

However, I want to be able to exclude records over a date range, for example if a customer goes on holiday.  For this, i have put two fields in the 'customers' table - 'holdstart' and 'holdend'. 

 

I tried adding the following to the end of the query:

 

AND '$DelDate' NOT BETWEEN holdstart AND holdend

 

However, it just gave the same four results.  Three of these results have no holdstart or holdend values, the other has a holdstart value of 2008-05-08 and holdend value of 2008-05-15.

 

I tried running the query in phpMyAdmin, substituting '$DelDate' with '2008-05-15'.  Again, I got the same 4 results.

 

How can I solve this?

 

Thanks in advance for any help offered!

 

[Barand]

Are you dates DATE or DATETIME

 

If DATETIME then the time element could put it outside the range.

 

if holdstart, holdend have no values then all dates are NOT BETWEEN them

[retro]

they are DATE.

 

Yes, the point is that I want any date that isn't between a range.  So my thinking was that if holdstart = "" and holdend = "", then everything should be NOT BETWEEN them, i.e. it would ignore that part of the query.

 

I'll try to (over)simplify:

 

Name:	John Smith	Fred Bloggs	Bert Jones
Del. Day:	Mondays	Tuesdays	Mondays
Holiday:	24-31 May 2008	n/a	n/a

 

If I were to run my query for 19 May 2008, it should tell me that John Smith and Bert Jones have deliveries due that day.

 

If I were to run my query for 26 May 2008, it should tell me that only Bert Jones has a delivery. 

 

I guess I'm not interpreting how NOT BETWEEN will work correctly.  In the above example, I'd feed in 2008-05-26 and it would determine that this lies between 2008-05-24 and 2008-05-31 and not display that record.  As this won't work, can you suggest how I can either tweak this, or another way to accomplish the same thing?

 

Thanks! 

[fenway]

Could you post the sample data pertaining to these 4?

[retro]

Certainly.

 

I'll use phpMyAdmin (incidentally, phpMyAdmin 2.10.0.2, MySQL Server version: 5.0.51a, MyISAM)

 

Query:

 

SELECT * 
FROM orders
LEFT JOIN customers ON ( customers.id = orders.customer ) 
WHERE (
recur =1
AND DAYOFWEEK( date ) = DAYOFWEEK( '2008-05-08' ) 
)
OR (
recur =2
AND DATEDIFF( date, '2008-05-08') %14 =0
)
AND date <= '2008-05-08'
AND '2008-05-08' NOT BETWEEN holdstart AND holdend

 

Result:

 

id  customer  date  item1  item2  item3  item4  item5  item6  item7  item8  item9  item10  recur  notes  holdstart  holdend  id  firstname  lastname  town  street  postcode  houseno   

4 6  2008-04-24 0 1 0 0 0 0 0 2 0 0 1  2008-05-08 2008-05-15 6 Eric Someone  Dover Fish Street AB12 3FG 1         

5 2  2008-04-24 0 1 0 0 0 0 0 0 0 0 1  0000-00-00 0000-00-00 2 Bert Smith  Folkestone Some Lane CT19 1BC 23         

7 2  2008-04-24 0 0 0 0 0 0 0 0 1 0 1  0000-00-00 0000-00-00 2 Bert Smith  Folkestone Some Lane CT19 1BC 23         

18 8  2008-05-01 0 1 0 0 0 0 0 0 0 0 1  0000-00-00 0000-00-00 8 Tom Dickson  Dover Somefield Avenue  52         

 

As you can see, I substituted $DelDate with 2008-05-08, which is the value for holdend in order id 4.

[Barand]

What does this give

 

var_dump($DelDate);

[retro]

If I choose May 5th, it gives:

 

string(10) "2008-05-08"

 

If I choose May 15, it gives:

 

string(10) "2008-05-15"

 

etc.

[Barand]

try

SELECT * 
FROM orders
LEFT JOIN customers ON ( customers.id = orders.customer ) 
WHERE ((
recur =1
AND DAYOFWEEK( date ) = DAYOFWEEK( '2008-05-08' ) 
)
OR (
recur =2
AND DATEDIFF( date, '2008-05-08') %14 =0
))
AND date <= '2008-05-08'
AND '2008-05-08' NOT BETWEEN holdstart AND holdend

[retro]

OOH!  That has returned 3 results, missing out record id 4!

 

Thanks very much for the help! 

[Barand]

I should've spotted it earlier as I always preach the use of (..) when mixing AND and OR in WHERE clauses.

 

Explanation:

 

A OR B AND C

 

defaults to A OR (B AND C)

 

In you case you needed

 

(A OR B) AND C