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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html