* Dusty Kleyboecker > I have two tables, a PROJECTS table and a COMMENTS table linked by > seq in a one to many relationship. In other words, there might be > several comment rows for the same project, with different dates. > > PROJECTS > > seq name startdate enddate > > 1 p1 02032002 12202003 > 2 p2 04012003 10012003 > 3 p3 01012003 12312003 > > > COMMENTS > > seq date comment > > 1 09/15/2002 Here is a comment. > 2 07/09/2003 Another comment. > 2 08/15/2003 Second comment for this project. > 3 05/15/2003 Here is a comment. > > I want to join these tables only taking the most recent comment > and date from the comment table. > > I have been looking at the MAX() function, LIMIT, DISTINCT, but am > having trouble. I am using MYSQL version 3.23.58 on linux.
There are a couple of ways to solve this. You could use temporary tables or the MAX-CONCAT trick described in the manual: <URL: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html > Another way could be to join the comment table twice, something like this: SELECT PROJECTS.*,COMMENTS.* FROM PROJECTS LEFT JOIN COMMENTS ON COMMENTS.seq = PROJECTS.seq LEFT JOIN COMMENTS C2 ON C2.seq = PROJECTS.seq AND C2.date > COMMENTS.date WHERE C2.seq IS NULL Note that this can be heavy on the server if there are very many comments per project, because each comment is joined with every other comment with a later date. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
