[thelist] mysql: max in joins?

Emma Jane Hogbin emmajane at xtrinsic.com
Tue Mar 4 13:54:01 CST 2003


Hey all:

Does anyone know off hand if you can have a max() as part of a join query
in mysql? I'm using: mysql  Ver 11.18 Distrib 3.23.55, for pc-linux-gnu
(i686)

Here's the query and the error. I'm not sure if I've even pin-pointed the
right problem in the query.

mysql> select meta_articles.id, meta_articles.subject, meta_articles.created,
    -> meta_comments.max(created) as updated, meta_comments.name as who
    -> from meta_comments
    -> left join meta_articles on meta_comments.articles_id = meta_articles.id
    -> where meta_articles.cats_id = 1 group by id order by created ASC;
ERROR 1064: You have an error in your SQL syntax near '(created) as updated, meta_comments.name as who
from meta_comments
left join met' at line 2

It seems like mysql doesn't like max combined with a table name. See:
mysql> select max(created) as updated from meta_comments;
+---------------------+
| updated             |
+---------------------+
| 2002-12-28 15:21:01 |
+---------------------+
1 row in set (0.00 sec)

mysql> select meta_comments.max(created) as updated from meta_comments;
ERROR 1064: You have an error in your SQL syntax near '(created) as
updated from meta_comments' at line 1

What I'm looking for is information about the article INCLUDING who and
when last commented on it. I know how to do it as separate queries, but I
thought it would be more efficient to combine the queries.

Ideas on how to get this query to work? Or does it need to be two
different queries? I'm still playing with the db structure (I'm
"upgrading" an old app). If it's easiest I can just put the "last comment"
date in the meta_articles table. phpBB and drupal appear to just put the
last_comment_id into the (equivalent) table. They probably then do a left
join to get the comment info for that id.

Thoughts?

emma <-- I install software just to get database layouts

--
Emma Jane Hogbin
[[ 416 417 2868 ][ www.xtrinsic.com ]]



More information about the thelist mailing list