* Ville Mattila > I have a table including information about my projects, the structure > has each id and name. Then I have another table including status > information of each project: entryid, projectid, status and timestamp. > > Is there any possibility to fetch a list of projects with the most > recent status by one query? I can do it of course by two different > queries, but I don't find it as very good solution.
Take a look at this: <URL: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html > It can also be done with joins in some cases, basically joining the status table twice, something like this: SELECT P.*,S1.* FROM projects P LEFT JOIN status_info S1 ON S1.PID = P.PID LEFT JOIN status_info S2 ON S2.PID = P.PID AND S2.datecol > S1.datecol WHERE ISNULL(S2.PID) This will also list projects with no status info. If you don't want this, change the first LEFT JOIN to a normal join. When I say "in some cases" it is because this can be very heavy on the server if there are many status_info rows per project. 'Many' in this case means hundreds, but this will depend on the server & the data. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
