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

On 04/01/2006, "Florin C." wrote:

> Can you help me write this in another way

With temporary tables, perhaps (untested):

DROP TABLE IF EXISTS t_acl, t_rec;

CREATE TEMPORARY TABLE t_acl (PRIMARY KEY (campaign))
SELECT 
	COUNT(DISTINCT username) AS no_of_users, 
	campaign
FROM map_acl
GROUP BY campaign;

CREATE TEMPORARY TABLE t_rec (PRIMARY KEY (campaign))
SELECT 
	COUNT(DISTINCT recordno) AS no_of_records, 
	campaign
FROM `records`
GROUP BY campaign;

SELECT 
	main_campaigns.*, 
	main_status.value, 
	t_acl.no_of_users, 
	t_rec.no_of_records
FROM main_campaigns
LEFT JOIN main_status ON main_campaigns.status = main_status.id
LEFT JOIN t_acl ON main_campaigns.id = t_acl.campaign
LEFT JOIN t_rec ON main_campaigns.id = t_rec.campaign
LIMIT 0 , 30;

DROP TABLE IF EXISTS t_acl, t_rec;


-- 
felix

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