RE: Query works on mysql 4.1.16 but return error on 4.0.22

I think it should be like this. DISTINCT is not a function call.

SELECT main_campaigns. * , main_status.value, acl.no_of_users,
rec.no_of_records
FROM main_campaigns
LEFT JOIN main_status ON main_campaigns.status = main_status.id
LEFT JOIN (

SELECT COUNT(DISTINCT username) AS no_of_users, campaign
FROM map_acl
GROUP BY campaign
) AS acl ON main_campaigns.id = acl.campaign
LEFT JOIN (

SELECT COUNT(DISTINCT recordno) AS no_of_records, campaign
FROM `records`
GROUP BY campaign
) AS rec ON main_campaigns.id = rec.campaign
LIMIT 0 , 30

John B.

-----Original Message-----
From: Florin C. [mailto:[email protected]] 
Sent: Wednesday, 4 January 2006 11:48 PM
To: [email protected]
Subject: Query works on mysql 4.1.16 but return error on 4.0.22

Hy, i have this query witch works great on mysql 4.1.16, but on mysql
4.0.22 it returns error:

ERROR 1064: You have an error in your SQL syntax.  Check the manual
that corresponds to your MySQL server version for the right syntax to
use near 'SELECT COUNT( DISTINCT (
username
) ) AS no_of_users, campaign


here is the query:



SELECT main_campaigns. * , main_status.value, acl.no_of_users,
rec.no_of_records
FROM main_campaigns
LEFT JOIN main_status ON main_campaigns.status = main_status.id
LEFT JOIN (

SELECT COUNT( DISTINCT (
username
) ) AS no_of_users, campaign
FROM map_acl
GROUP BY campaign
) AS acl ON main_campaigns.id = acl.campaign
LEFT JOIN (

SELECT COUNT( DISTINCT (
recordno
) ) AS no_of_records, campaign
FROM `records`
GROUP BY campaign
) AS rec ON main_campaigns.id = rec.campaign
LIMIT 0 , 30

please help me

--
===================================
Florin C.


-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/[email protected]