Обсуждение: group by weirdness
Could someome explain these error messages to me? Why am I being asked to group by j.id? And why is the subquery worriedabout ml.oid if ml.oid is used in an aggregate? Follows: script, then output. select version(); create table j (id int, created timestamp default current_timestamp, fkey int); create table mj (jid int, mid int); create table ml (jid int, created timestamp default current_timestamp, state int); insert into j (id, fkey) values (1, 1); insert into j (id, fkey) values (2, 1); insert into mj values(1, 1); insert into mj values(1, 2); insert into mj values(2, 3); insert into mj values(2, 4); insert into mj values(2, 5); insert into ml(jid, state) values (1, 2); insert into ml(jid, state) values (1, 2); insert into ml(jid, state) values (1, 2); insert into ml(jid, state) values (1, 2); insert into ml(jid, state) values (1, 11); insert into ml(jid, state) values (2, 2); insert into ml(jid, state) values (2, 2); insert into ml(jid, state) values (2, 11); select j.id, j.created, count(mj.mid), (select count(ml.oid) where ml.state <> 11), (select count(ml.oid) where ml.stateIN(2,5) ) FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id ; select j.id, j.created, count(mj.mid), (select count(ml.oid) where ml.state <> 11), (select count(ml.oid) where ml.stateIN(2,5) ) FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id group by j.id, j.created; drop table j; drop table mj ; drop table ml; =================================================================================================== playpen=# select version(); version --------------------------------------------------------------------- PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled byGCC egcs-2.91.66 (1 row) playpen=# create table j (id int, created timestamp default current_timestamp, fkey int); CREATE playpen=# create table mj (jid int, mid int); CREATE playpen=# create table ml (jid int, created timestamp default current_timestamp, state int); CREATE playpen=# playpen=# insert into j (id, fkey) values (1, 1); <snip> playpen=# insert into ml(jid, state) values (2, 11); INSERT 329676 1 playpen=# playpen=# select j.id, j.created, count(mj.mid), playpen-# (select count(ml.oid) where ml.state <> 11), playpen-# (select count(ml.oid) where ml.state IN(2,5) ) playpen-# FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id ; ERROR: Attribute j.id must be GROUPed or used in an aggregate function playpen=# playpen=# select j.id, j.created, count(mj.mid), playpen-# (select count(ml.oid) where ml.state <> 11), playpen-# (select count(ml.oid) where ml.state IN(2,5) ) playpen-# FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id playpen-# group by j.id, j.created; ERROR: Sub-SELECT uses un-GROUPed attribute ml.oid from outer query playpen=# playpen=# drop table j; DROP playpen=# drop table mj ; DROP playpen=# drop table ml; DROP -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com
Joseph,
The subject line could describe a lot of what I see outside my house
every day (I live in San Francisco CA).
> Could someome explain these error messages to me? Why am I being
> asked to group by j.id?
Because you've asked the db engine to count on mj.mid. The parser want
you to be specific about whether the other columns are being aggregated
or not.
> And why is the subquery worried about ml.oid if ml.oid is used in
> an aggregate?
> playpen=# select j.id, j.created, count(mj.mid),
> playpen-# (select count(ml.oid) where ml.state <> 11),
> playpen-# (select count(ml.oid) where ml.state IN(2,5) )
> playpen-# FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND
> ml.jid = j.id
> playpen-# group by j.id, j.created;
> ERROR: Sub-SELECT uses un-GROUPed attribute ml.oid from outer query
Because you're trying to aggregate two aggregates which are sub-selected
in the FROM clause ... a very painful way to not get the results you're
looking for. Even if you fixed the GROUPing problem, this query
wouldn't parse for other reasons. For example, the subselects you've
chosen would return the same count for every row, the total of ml.oid in
the database.
Try putting your sub-selects in the FROM clause instead. (Personally,
I've never found a use for sub-selects in the SELECT clause)
SELECT j.id, j.created, count(mj.mid), ma1.mcount, ma2.mcount
FROM j, mj,
(SELECTjid, COUNT(oid) as mcount FROM ml
WHERE ml.state <> 11 GROUP BY jid) ma1,
(SELECT jid, COUNT(oid) as mcount FROM ml
WHERE ml.state in (2,5) GROUP BY jid) ma2
WHERE j.fkey = 1 AND mj.jid = j.id
AND ma1.jid = j.id AND ma2.jid = j.id
GROUP BY j.id, j.created, ma1.mcount, ma2.mcount;
-Josh Berkus
PS. Thanks for providing such complete data with your question!
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
Вложения
Josh Berkus wrote: > Joseph, > > The subject line could describe a lot of what I see outside my house > every day (I live in San Francisco CA). > > >>Could someome explain these error messages to me? Why am I being >>asked to group by j.id? >> > > Because you've asked the db engine to count on mj.mid. The parser want > you to be specific about whether the other columns are being aggregated > or not. But they are in seperate tables, so how could it think they could be aggregated together? -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com
Josh Berkus wrote: > Try putting your sub-selects in the FROM clause instead. (Personally, > I've never found a use for sub-selects in the SELECT clause) > > SELECT j.id, j.created, count(mj.mid), ma1.mcount, ma2.mcount > FROM j, mj, > (SELECTjid, COUNT(oid) as mcount FROM ml > WHERE ml.state <> 11 GROUP BY jid) ma1, > (SELECT jid, COUNT(oid) as mcount FROM ml > WHERE ml.state in (2,5) GROUP BY jid) ma2 > WHERE j.fkey = 1 AND mj.jid = j.id > AND ma1.jid = j.id AND ma2.jid = j.id > GROUP BY j.id, j.created, ma1.mcount, ma2.mcount; > OK that worked for this simple example, but on my real database the performance was horrible, and it didn't work for then there were zero entries in ml (this bites me sometimes, when the AND clause keeps things from working as I think they should). Putting the selects in the SELECT solved both problems. I took out the 'AND ml.jid = j.id' from the outer WHERE (would have also excluded cases where there were zero entries in ml) and only refrenced ml in the subselect. Thanks for your help. -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com
Would this give you what you want? SELECT j.id, j.created, COUNT(mj.mid), SUM(CASE WHEN ml.state <> 11 THEN 1 ELSE 0 END) AS tally_1, SUM (CASEWHEN ml.state IN(2,5) THEN 1 ELSE 0 END)AS tally_2 FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid= j.id;
Joseph, you might want to try: CREATE VIEW mj1 (jid, cnt) AS SELECT jid, COUNT(mid) cnt FROM mj GROUP BY jid; CREATE VIEW ml1 (jid, cnt) AS SELECT jid, COUNT(*) cnt FROM ml WHERE state <> 11 GROUP BY jid; CREATE VIEW ml2 (jid, cnt) AS SELECT jid, COUNT(*) cnt FROM ml WHERE state IN (2,5) GROUP BY jid; SELECT j.id, j.created, mj1.cnt, ml1.cnt, ml2.cnt FROM j LEFT JOIN mj1 ON (j.id = mj1.jid) LEFT JOIN ml1 ON (j.id = ml1.jid) LEFT JOIN ml2 ON (j.id = ml2.jid) WHERE j.fkey = 1; I did not test this with PostgreSQL, but you get the idea. Probably PG is even smart enough to handle it all in one: SELECT j.id, j.created, mj1.cnt, ml1.cnt, ml2.cnt FROM j LEFT JOIN (SELECT jid, COUNT(mid) cnt FROM mj GROUP BY jid) mj1 ON (j.id = mj1.jid) LEFT JOIN (SELECT jid, COUNT(*) cnt FROM ml WHERE state <> 11 GROUP BY jid) ml1 ON (j.id =ml1.jid) LEFT JOIN (SELECT jid, COUNT(*) cnt FROM ml WHERE state IN (2, 5) GROUP BY jid) ml2 ON (j.id= ml2.jid) WHERE j.fkey = 1; HTH,Carl van Tast On Tue, 11 Sep 2001 02:26:32 +0000 (UTC), jks@selectacast.net (Joseph Shraibman) wrote: >Could someome explain these error messages to me? Why am I being asked to group by j.id? > And why is the subquery worried about ml.oid if ml.oid is used in an aggregate? > >Follows: script, then output. > > >select version(); >create table j (id int, created timestamp default current_timestamp, fkey int); >create table mj (jid int, mid int); >create table ml (jid int, created timestamp default current_timestamp, state int); > >insert into j (id, fkey) values (1, 1); >insert into j (id, fkey) values (2, 1); > >insert into mj values(1, 1); >insert into mj values(1, 2); >insert into mj values(2, 3); >insert into mj values(2, 4); >insert into mj values(2, 5); > >insert into ml(jid, state) values (1, 2); >insert into ml(jid, state) values (1, 2); >insert into ml(jid, state) values (1, 2); >insert into ml(jid, state) values (1, 2); >insert into ml(jid, state) values (1, 11); >insert into ml(jid, state) values (2, 2); >insert into ml(jid, state) values (2, 2); >insert into ml(jid, state) values (2, 11); > >select j.id, j.created, count(mj.mid), > (select count(ml.oid) where ml.state <> 11), > (select count(ml.oid) where ml.state IN(2,5) ) >FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id ; > >select j.id, j.created, count(mj.mid), > (select count(ml.oid) where ml.state <> 11), > (select count(ml.oid) where ml.state IN(2,5) ) >FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id >group by j.id, j.created; > >drop table j; >drop table mj ; >drop table ml; > >=================================================================================================== > >playpen=# select version(); > version >--------------------------------------------------------------------- > PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66 >(1 row) > >playpen=# create table j (id int, created timestamp default current_timestamp, fkey int); >CREATE >playpen=# create table mj (jid int, mid int); >CREATE >playpen=# create table ml (jid int, created timestamp default current_timestamp, state int); >CREATE >playpen=# >playpen=# insert into j (id, fkey) values (1, 1); ><snip> >playpen=# insert into ml(jid, state) values (2, 11); >INSERT 329676 1 >playpen=# >playpen=# select j.id, j.created, count(mj.mid), >playpen-# (select count(ml.oid) where ml.state <> 11), >playpen-# (select count(ml.oid) where ml.state IN(2,5) ) >playpen-# FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id ; >ERROR: Attribute j.id must be GROUPed or used in an aggregate function >playpen=# >playpen=# select j.id, j.created, count(mj.mid), >playpen-# (select count(ml.oid) where ml.state <> 11), >playpen-# (select count(ml.oid) where ml.state IN(2,5) ) >playpen-# FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id >playpen-# group by j.id, j.created; >ERROR: Sub-SELECT uses un-GROUPed attribute ml.oid from outer query >playpen=# >playpen=# drop table j; >DROP >playpen=# drop table mj ; >DROP >playpen=# drop table ml; >DROP
Joseph, you might want to try: CREATE VIEW mj1 (jid, cnt) AS SELECT jid, COUNT(mid) cnt FROM mj GROUP BY jid; CREATE VIEW ml1 (jid, cnt) AS SELECT jid, COUNT(*) cnt FROM ml WHERE state <> 11 GROUP BY jid; CREATE VIEW ml2 (jid, cnt) AS SELECT jid, COUNT(*) cnt FROM ml WHERE state IN (2,5) GROUP BY jid; SELECT j.id, j.created, mj1.cnt, ml1.cnt, ml2.cnt FROM j LEFT JOIN mj1 ON (j.id = mj1.jid) LEFT JOIN ml1 ON (j.id = ml1.jid) LEFT JOIN ml2 ON (j.id = ml2.jid) WHERE j.fkey = 1; I did not test this with PostgreSQL, but you get the idea. Probably PG is even smart enough to handle it all in one: SELECT j.id, j.created, mj1.cnt, ml1.cnt, ml2.cnt FROM j LEFT JOIN (SELECT jid, COUNT(mid) cnt FROM mj GROUP BY jid) mj1 ON (j.id = mj1.jid) LEFT JOIN (SELECT jid, COUNT(*) cnt FROM ml WHERE state <> 11 GROUP BY jid) ml1 ON (j.id =ml1.jid) LEFT JOIN (SELECT jid, COUNT(*) cnt FROM ml WHERE state IN (2, 5) GROUP BY jid) ml2 ON (j.id= ml2.jid) WHERE j.fkey = 1; HTH,Carl van Tast On Tue, 11 Sep 2001 02:26:32 +0000 (UTC), jks@selectacast.net (Joseph Shraibman) wrote: >Could someome explain these error messages to me? Why am I being asked to group by j.id? > And why is the subquery worried about ml.oid if ml.oid is used in an aggregate? > >Follows: script, then output. > > >select version(); >create table j (id int, created timestamp default current_timestamp, fkey int); >create table mj (jid int, mid int); >create table ml (jid int, created timestamp default current_timestamp, state int); > >insert into j (id, fkey) values (1, 1); >insert into j (id, fkey) values (2, 1); > >insert into mj values(1, 1); >insert into mj values(1, 2); >insert into mj values(2, 3); >insert into mj values(2, 4); >insert into mj values(2, 5); > >insert into ml(jid, state) values (1, 2); >insert into ml(jid, state) values (1, 2); >insert into ml(jid, state) values (1, 2); >insert into ml(jid, state) values (1, 2); >insert into ml(jid, state) values (1, 11); >insert into ml(jid, state) values (2, 2); >insert into ml(jid, state) values (2, 2); >insert into ml(jid, state) values (2, 11); > >select j.id, j.created, count(mj.mid), > (select count(ml.oid) where ml.state <> 11), > (select count(ml.oid) where ml.state IN(2,5) ) >FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id ; > >select j.id, j.created, count(mj.mid), > (select count(ml.oid) where ml.state <> 11), > (select count(ml.oid) where ml.state IN(2,5) ) >FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id >group by j.id, j.created; > >drop table j; >drop table mj ; >drop table ml; > >=================================================================================================== > >playpen=# select version(); > version >--------------------------------------------------------------------- > PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66 >(1 row) > >playpen=# create table j (id int, created timestamp default current_timestamp, fkey int); >CREATE >playpen=# create table mj (jid int, mid int); >CREATE >playpen=# create table ml (jid int, created timestamp default current_timestamp, state int); >CREATE >playpen=# >playpen=# insert into j (id, fkey) values (1, 1); ><snip> >playpen=# insert into ml(jid, state) values (2, 11); >INSERT 329676 1 >playpen=# >playpen=# select j.id, j.created, count(mj.mid), >playpen-# (select count(ml.oid) where ml.state <> 11), >playpen-# (select count(ml.oid) where ml.state IN(2,5) ) >playpen-# FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id ; >ERROR: Attribute j.id must be GROUPed or used in an aggregate function >playpen=# >playpen=# select j.id, j.created, count(mj.mid), >playpen-# (select count(ml.oid) where ml.state <> 11), >playpen-# (select count(ml.oid) where ml.state IN(2,5) ) >playpen-# FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id >playpen-# group by j.id, j.created; >ERROR: Sub-SELECT uses un-GROUPed attribute ml.oid from outer query >playpen=# >playpen=# drop table j; >DROP >playpen=# drop table mj ; >DROP >playpen=# drop table ml; >DROP
Joseph, you might want to try: CREATE VIEW mj1 (jid, cnt) AS SELECT jid, COUNT(mid) cnt FROM mj GROUP BY jid; CREATE VIEW ml1 (jid, cnt) AS SELECT jid, COUNT(*) cnt FROM ml WHERE state <> 11 GROUP BY jid; CREATE VIEW ml2 (jid, cnt) AS SELECT jid, COUNT(*) cnt FROM ml WHERE state IN (2,5) GROUP BY jid; SELECT j.id, j.created, mj1.cnt, ml1.cnt, ml2.cnt FROM j LEFT JOIN mj1 ON (j.id = mj1.jid) LEFT JOIN ml1 ON (j.id = ml1.jid) LEFT JOIN ml2 ON (j.id = ml2.jid) WHERE j.fkey = 1; I did not test this with PostgreSQL, but you get the idea. Probably PG is even smart enough to handle it all in one: SELECT j.id, j.created, mj1.cnt, ml1.cnt, ml2.cnt FROM j LEFT JOIN (SELECT jid, COUNT(mid) cnt FROM mj GROUP BY jid) mj1 ON (j.id = mj1.jid) LEFT JOIN (SELECT jid, COUNT(*) cnt FROM ml WHERE state <> 11 GROUP BY jid) ml1 ON (j.id =ml1.jid) LEFT JOIN (SELECT jid, COUNT(*) cnt FROM ml WHERE state IN (2, 5) GROUP BY jid) ml2 ON (j.id= ml2.jid) WHERE j.fkey = 1; HTH,Carl van Tast On Tue, 11 Sep 2001 02:26:32 +0000 (UTC), jks@selectacast.net (Joseph Shraibman) wrote: >Could someome explain these error messages to me? Why am I being asked to group by j.id? > And why is the subquery worried about ml.oid if ml.oid is used in an aggregate? > >Follows: script, then output. > > >select version(); >create table j (id int, created timestamp default current_timestamp, fkey int); >create table mj (jid int, mid int); >create table ml (jid int, created timestamp default current_timestamp, state int); > >insert into j (id, fkey) values (1, 1); >insert into j (id, fkey) values (2, 1); > >insert into mj values(1, 1); >insert into mj values(1, 2); >insert into mj values(2, 3); >insert into mj values(2, 4); >insert into mj values(2, 5); > >insert into ml(jid, state) values (1, 2); >insert into ml(jid, state) values (1, 2); >insert into ml(jid, state) values (1, 2); >insert into ml(jid, state) values (1, 2); >insert into ml(jid, state) values (1, 11); >insert into ml(jid, state) values (2, 2); >insert into ml(jid, state) values (2, 2); >insert into ml(jid, state) values (2, 11); > >select j.id, j.created, count(mj.mid), > (select count(ml.oid) where ml.state <> 11), > (select count(ml.oid) where ml.state IN(2,5) ) >FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id ; > >select j.id, j.created, count(mj.mid), > (select count(ml.oid) where ml.state <> 11), > (select count(ml.oid) where ml.state IN(2,5) ) >FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id >group by j.id, j.created; > >drop table j; >drop table mj ; >drop table ml; > >=================================================================================================== > >playpen=# select version(); > version >--------------------------------------------------------------------- > PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66 >(1 row) > >playpen=# create table j (id int, created timestamp default current_timestamp, fkey int); >CREATE >playpen=# create table mj (jid int, mid int); >CREATE >playpen=# create table ml (jid int, created timestamp default current_timestamp, state int); >CREATE >playpen=# >playpen=# insert into j (id, fkey) values (1, 1); ><snip> >playpen=# insert into ml(jid, state) values (2, 11); >INSERT 329676 1 >playpen=# >playpen=# select j.id, j.created, count(mj.mid), >playpen-# (select count(ml.oid) where ml.state <> 11), >playpen-# (select count(ml.oid) where ml.state IN(2,5) ) >playpen-# FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id ; >ERROR: Attribute j.id must be GROUPed or used in an aggregate function >playpen=# >playpen=# select j.id, j.created, count(mj.mid), >playpen-# (select count(ml.oid) where ml.state <> 11), >playpen-# (select count(ml.oid) where ml.state IN(2,5) ) >playpen-# FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id >playpen-# group by j.id, j.created; >ERROR: Sub-SELECT uses un-GROUPed attribute ml.oid from outer query >playpen=# >playpen=# drop table j; >DROP >playpen=# drop table mj ; >DROP >playpen=# drop table ml; >DROP
On Sat, 15 Sep 2001 00:26:01 +0200, I wrote: > [...] >CREATE VIEW mj1 (jid, cnt) AS >SELECT jid, COUNT(mid) cnt FROM mj GROUP BY jid; This should be COUNT(mid) AS cnt ... > [...] >I did not test this with PostgreSQL, but you get the idea. Well, now I did test with PostgreSQL (thanks, Jason Tishler, for your Cygwin PostgreSQL README!). PG does not support column aliases without "AS". >Probably PG is even smart enough to handle it all in one: Sure it is. So, Joseph, your solution is: SELECT j.id, j.created, mj1.cnt, ml1.cnt, ml2.cnt FROM j LEFT JOIN (SELECT jid, COUNT(mid) AS cnt FROM mj GROUP BY jid) mj1 ON (j.id = mj1.jid) LEFT JOIN (SELECT jid, COUNT(*) AS cnt FROM ml WHERE state <> 11 GROUP BY jid) ml1 ON (j.id= ml1.jid) LEFT JOIN (SELECT jid, COUNT(*) AS cnt FROM ml WHERE state IN (2, 5) GROUP BY jid) ml2 ON (j.id= ml2.jid) WHERE j.fkey = 1; HTH,Carl van Tast
On Sat, 15 Sep 2001 00:26:01 +0200, I wrote: > [...] >CREATE VIEW mj1 (jid, cnt) AS >SELECT jid, COUNT(mid) cnt FROM mj GROUP BY jid; This should be COUNT(mid) AS cnt ... > [...] >I did not test this with PostgreSQL, but you get the idea. Well, now I did test with PostgreSQL (thanks, Jason Tishler, for your Cygwin PostgreSQL README!). PG does not support column aliases without "AS". >Probably PG is even smart enough to handle it all in one: Sure it is. So, Joseph, your solution is: SELECT j.id, j.created, mj1.cnt, ml1.cnt, ml2.cnt FROM j LEFT JOIN (SELECT jid, COUNT(mid) AS cnt FROM mj GROUP BY jid) mj1 ON (j.id = mj1.jid) LEFT JOIN (SELECT jid, COUNT(*) AS cnt FROM ml WHERE state <> 11 GROUP BY jid) ml1 ON (j.id= ml1.jid) LEFT JOIN (SELECT jid, COUNT(*) AS cnt FROM ml WHERE state IN (2, 5) GROUP BY jid) ml2 ON (j.id= ml2.jid) WHERE j.fkey = 1; HTH,Carl van Tast
On Sat, 15 Sep 2001 00:26:01 +0200, I wrote: > [...] >CREATE VIEW mj1 (jid, cnt) AS >SELECT jid, COUNT(mid) cnt FROM mj GROUP BY jid; This should be COUNT(mid) AS cnt ... > [...] >I did not test this with PostgreSQL, but you get the idea. Well, now I did test with PostgreSQL (thanks, Jason Tishler, for your Cygwin PostgreSQL README!). PG does not support column aliases without "AS". >Probably PG is even smart enough to handle it all in one: Sure it is. So, Joseph, your solution is: SELECT j.id, j.created, mj1.cnt, ml1.cnt, ml2.cnt FROM j LEFT JOIN (SELECT jid, COUNT(mid) AS cnt FROM mj GROUP BY jid) mj1 ON (j.id = mj1.jid) LEFT JOIN (SELECT jid, COUNT(*) AS cnt FROM ml WHERE state <> 11 GROUP BY jid) ml1 ON (j.id= ml1.jid) LEFT JOIN (SELECT jid, COUNT(*) AS cnt FROM ml WHERE state IN (2, 5) GROUP BY jid) ml2 ON (j.id= ml2.jid) WHERE j.fkey = 1; HTH,Carl van Tast