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

[Sxooter]

Your current where clause is equivalent to this:

Where
( sale_type_landsale.sale_date >= '2006-01-01'
AND sale_type_landsale.sale_date <= '2007-06-08'
AND sale_type_landsale.city ='4'
AND connector_sale.ref = 'sale_type_landsale'
AND sale.sale_archive <> 1)
OR sale_type_landsale.city ='13'

Becuase OR clauses evaluate first.

[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

[bradgrafelman]

Does that mean your problem is resolved? If so, don't forget to mark this thread resolved.