Mysql query returning incorrectly

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

Code (markup):

 

Group the AND's and OR's

Try this one :

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

Code (markup):

 

think you might need some brackets there, i'm not sure exactly what you want your query to return but 'or' takes precedence over 'and'.

maybe this what you want ?

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

 

thanks jmafonseca, that worked.

 

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

Code (markup):

 

Sure, glad it worked

(smithy suggested the same and i'd guess it works too.)

 

Will not the above query return results from June 09th aswell?

The query should be
sale_type_landsale.sale_date < '2007-06-08 23:59:59'
link_dev

 

link_dev your good. o yeah real good. thanks I'll make the change

 

Ooops it's a DATETIME, not a DATE field?

 

As long as you get the right results....