[SOLVED] Mysql query returning incorrectly

[cesarcesar]

The following MySql Query is not retuning correctly. It is returning dates outside the given range. Why? Thanks.

 

SELECT distinct
sale_type_landsale.sale_type_landsale_id
,sale_type_landsale.sale_date,sale_type_landsale.city,sale_type_landsale.name,sale_type_landsale.sale_price,sale_type_landsale.number_lots
FROM
sale_type_landsale
Left Outer Join connector_sale ON sale_type_landsale.sale_type_landsale_id = connector_sale.ref_id
Left Outer Join sale ON connector_sale.sale_id = sale.sale_id
Where 
sale_type_landsale.sale_date >= '2006-01-01' 
AND sale_type_landsale.sale_date <= '2007-06-08' 
AND sale_type_landsale.city ='4' 
OR sale_type_landsale.city ='13'  
AND connector_sale.ref = 'sale_type_landsale' 
AND sale.sale_archive <> 1

[cesarcesar]

solution:

SELECT distinct
sale_type_landsale.sale_type_landsale_id
,sale_type_landsale.sale_date,sale_type_landsale.city,sale_type_landsale.name,sale_type_landsale.sale_price,sale_type_landsale.number_lots
FROM
sale_type_landsale
Left Outer Join connector_sale ON sale_type_landsale.sale_type_landsale_id = connector_sale.ref_id
Left Outer Join sale ON connector_sale.sale_id = sale.sale_id
Where ( sale_type_landsale.sale_date >= '2006-01-01' AND sale_type_landsale.sale_date <= '2007-06-08' )
AND ( sale_type_landsale.city ='4' OR sale_type_landsale.city ='13' )
AND connector_sale.ref = 'sale_type_landsale' 
AND sale.sale_archive <> 1

[Wildbug]

The parentheses around the date AND clause are not necessary (but there is nothing wrong with them, and they're fine for their visual effect).  It was the parens around the OR clause that fixed your problem.  AND "binds" more tightly than OR, so MySQL interpreted the ANDs before the OR and the ANDs after the OR and matched either of them, not both, as you had intended.

 

(Excuse me if you know this already, just posting for other's reference.)