MySQL Where Not Working Correctly

Databases
1

Hi all, I seem to be having a couple of issues with a query. For some reason, it fetches random orders instead of those with the product_id = 2. Not to sure why this is happening. This is part of a booking system but is proving very frustrating! Any help would be hugely appreciated :slight_smile:

SELECT * FROM hire_order AS o 
LEFT JOIN hire_order_products AS op 
ON o.order_id = op.order_id 
WHERE product_id = 2 AND 
post_date BETWEEN ('2012-08-17') AND ('2012-08-23') OR 
home_date BETWEEN ('2012-08-17') AND ('2012-08-23')
AND order_returned != 2

There seems to be a problem with the dates, for when I remove the dates BETWEEN it works, but I need the dates to grab the correct availability.

2

Without trying it it’s very tricky but do the ‘random’ results fall between the home_date query? This isn’t optimised but start by putting all brackets in place:

SELECT * FROM hire_order AS o
LEFT JOIN hire_order_products AS op
ON o.order_id = op.order_id
WHERE product_id = 2 AND
((post_date BETWEEN ('2012-08-17') AND ('2012-08-23')) OR
(home_date BETWEEN ('2012-08-17') AND ('2012-08-23')))
AND order_returned != 2
3

Hi RichardAskew, that’s absolutely brilliant, it’s working like an absolute charm now, thought it may have something to do with brackets. Thank you so much, it’s hugely appreciated and a very quick response also. You’ve made my week, I can now get my new website up!

4

No problem!

I believe it was previously looking for:

product_id = 2 AND
post_date BETWEEN ('2012-08-17') AND ('2012-08-23')

OR

home_date BETWEEN ('2012-08-17') AND ('2012-08-23')
AND order_returned != 2
5

If product_id is a column of the hire_order_products tables, then you can change that LEFT JOIN in an INNER JOIN.
Or you can move any conditions that use columns from the left joined table to the ON clause, but I don’t think you want data from the hire_order table that doesn’t have any corresponding rows in the hire_order_products table?