debug sql

[esthera]

I'm trying to create an sql statement.

here's my statement that works in mssql ---

select count(customerid) as countcustomer from customer where customerid in(SELECT customerid from customerofferlink where offerid=1) and mytimestamp BETWEEN '2005-01-04' AND '2005-08-2005')

can someone help me convert it to mysql?

[devinemke]

if you are using a version of mySQL that supports subqueries (>= 4.1) then that query would work fine.

[esthera]

i don't know what version it is (it's on a hosted machine)
but i get an error You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use

any other way to do it?

[devinemke]

i don't know what version it is (it's on a hosted machine)
so then check the version yourself: mysql_get_server_info (http://www.php.net/manual/en/function.mysql-get-server-info.php)

[esthera]

MySQL server version: 4.0.24-standard

so is there any other way around this?

[raf]

MySQL server version: 4.0.24-standard

so is there any other way around this?

try

SELECT COUNT(customerid) AS countcustomer
FROM customer INNER JOIN customerofferlink ON customer.customerid = customerofferlink.customerid
WHERE customerofferlink.offerid=1 and customerofferlink.mytimestamp BETWEEN '2005-01-04' AND '2005-08-2005'

I don't know your db-design so the relations might not be the correct one, but the only alternative to the subselect is a join (or two seperate querys). By the way: a query with a join is faster then the equivalent with a subselect...

[esthera]

this doesn't work.

Let me explain to you my table desing

I have a customer table with name,email,mytimestamp

offers table has offerid,offer description

customerofferlink is a join table between customer and offers (has id,customerid, and offerid)

in this sql i want to count all customers for a specific offer that signed up between the 2 dates.

Can you help me?

[raf]

isn't is just

SELECT COUNT(*) as numcustomers FROM customer INNER JOIN customerofferlink ON customer.customerid = customerofferlink.customerid WHERE (customerofferlink.offerid=1) AND (customer.mytimestamp BETWEEN '2005-01-04' AND '2005-08-2005')

[esthera]

SELECT COUNT( * ) AS numcustomers FROM customer INNER JOIN customerofferlink ON customer.customerid = customerofferlink.customerid WHERE (customerofferlink.offerid =10) AND (customer.mytimestamp BETWEEN '2005-01-04' AND '2005-08-01')

seems to work but is returnign records dates 2005-08-05
is there somethign wrong?

[raf]

what datatype do you have for that mytimestamp?

else try
... TO_DAYS(mytimestamp) BETWEEN TO_DAYS('2005-01-04') AND TO_DAYS('2005-08-01')

[esthera]

it's a date time field