Query works on mysql 4.1.16 but return error on 4.0.22

am 04.01.2006 14:18:16 von florin.c

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_re=
cords
FROM main_campaigns
LEFT JOIN main_status ON main_campaigns.status =3D 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 =3D acl.campaign
LEFT JOIN (

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

please help me

--
==================== =====3D=
==========
Florin C.

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

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

am 04.01.2006 14:27:24 von Felix Geerinckx

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

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

MySQL 4.0.x does not support subqueries.

--
felix

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

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

am 04.01.2006 14:36:08 von florin.c

Can you help me write this in another way

On 4 Jan 2006 13:27:24 -0000, Felix Geerinckx w=
rote:
> On 04/01/2006, "Florin C." wrote:
>
> > Hy, i have this query witch works great on mysql 4.1.16, but on mysql
> > 4.0.22 it returns error:
>
> MySQL 4.0.x does not support subqueries.
>
>
> --
> felix
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=3Dflorin.c@gmail.co=
m
>
>

--
==================== =====3D=
==========
Florin C.
Mobil 0721.075.787

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

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

am 04.01.2006 15:38:15 von Felix Geerinckx

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/win32?unsub=gcdmw-win32@m.gmane.org

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

am 04.01.2006 22:43:28 von jbonnett

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 =3D 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 =3D acl.campaign
LEFT JOIN (

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

John B.

-----Original Message-----
From: Florin C. [mailto:florin.c@gmail.com]=20
Sent: Wednesday, 4 January 2006 11:48 PM
To: win32@lists.mysql.com
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 =3D 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 =3D acl.campaign
LEFT JOIN (

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

please help me

--
==================== =====3D=
==========
Florin C.

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

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

am 05.01.2006 09:26:21 von florin.c

On 4 Jan 2006 14:38:15 -0000, Felix Geerinckx w=
rote:
> On 04/01/2006, "Florin C." wrote:
10x, it work with temporary tables, i forgot that i can do temp tables.

> > 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 =3D main_status.id
> LEFT JOIN t_acl ON main_campaigns.id =3D t_acl.campaign
> LEFT JOIN t_rec ON main_campaigns.id =3D 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/win32?unsub=3Dflorin.c@gmail.co=
m
>
>

--
==================== =====3D=
==========
Florin C.
Mobil 0721.075.787

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org