Hi Shaun!
> 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;
>
> So if Project A has 3 tasks and Project B has 4 tasks I need
> the query to
> return 4.
This question is very easy to answer, but I think it's very difficult to
explain to you. For the first thing, you haven't added a GROUP BY clause,
which is the first thing you should do to begin with. It's strange you
didn't put an GROUP BY function, and if you don't know how it works, please
read a book on SQL first.
Given the tables you have, this query will do the work, without subqueries:
SELECT P.Project_ID, COUNT(T.Project_ID) as HowMany
FROM Projects P
LEFT JOIN Tasks T USING(Project_ID)
GROUP BY P.Project_ID
ORDER BY HowMany DESC
LIMIT 0,1
Column 1 (ProjecT_ID) is the Project ID with the most assigned tasks, and
Column 2 (HowMany) lists how many taks are actually asigned.
It is worth pointing out two notes about the above query:
- The query will return 0 as "HowMany" and the Project_ID for the
project which has no task assigned if this is the case. I mean, thanks to
the LEFT JOIN, we take into account projects with no taks attached. (This is
not possible with a straigh join like yours).
- Thanks to the LIMIT clause, we get only the first result, which by
the way is one of the projects with the most assigned tasks. If you want to
further select which one of the projects with the most assigned tasks you
want, you must order by another column, like date (or filter out the
projects in the WHERE clause).
Given said that,
Cheers,
Jose Miguel.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
