Frederic Wenzel wrote:
On Sun, 17 Oct 2004 19:36:34 +0000, shaun thornburgh
<[EMAIL PROTECTED]> wrote:
A Project will have 1 or more tasks assigned to it. Using the following query, how can I modify it so that I can find out the largest number of tasks assigned to a group of projects.
SELECT P.*, T.*
FROM Projects P, Tasks T
WHERE P.Project_ID = T.Project_ID
AND Project_ID > 2;
A subselect may help:
SELECT MAX(rows) FROM (SELECT COUNT(Task_ID) AS rows FROM Tasks GROUP BY Project_ID) AS maxi;
Don't know ATM if it can be done more easily, but a query like this should probably work.
Fred
It can be done without a sub-query:
SELECT COUNT(T.Project_ID) as Yourvar FROM Projects P LEFT JOIN Tasks T USING(Project_ID) WHERE P.Project_ID > 2 GROUP BY P.Project_ID ORDER BY COUNT(T.Project_ID) DESC LIMIT 1 ;
That *should* work, barring any typos or ommisions I may have made. I used LEFT JOIN because of personal preference, it can be done other way(s).
Chris
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
