Help with Query

[ryanschefke]

Hello - I am trying to count the number of rows in my "visitors" table. When I add the lines "filter_countries.filterID='57' AND v.country!=filter_countries.country_code" it miscalculates the numbers. I have a "country" column in the visitors table and I only want to count the rows that do not have the same "country" value as rows in my "filter_countries" table with "filterID='57".

 

My attempt at the query is below. What am I doing wrong?

 

SELECT COUNT(v.visitorID), DATE_FORMAT(DATE_SUB(v.dateTime,INTERVAL 5 HOUR), '%a %c/%e/%y') as visitsOnDate, v.dateTime,
cast(DATE_SUB(v.dateTime,INTERVAL 5 HOUR) as date) as groupDate FROM visitors as v , filter_countries WHERE v.customerID='11222' AND
cast(DATE_SUB(v.dateTime,INTERVAL 5 HOUR) as date) BETWEEN '2008-09-22' AND '2008-09-29' AND filter_countries.filterID='57' AND 
v.country!=filter_countries.country_code GROUP BY groupDate ORDER BY v.dateTime DESC

 

Thank you very much,

Ryan

 

[fenway]

First, you probably don't need to case all of those -- mysql deals with datetime<=>date conversions quite well.

 

Second, what are you trying to achieve?