problem constructing a mysql query(most recent per item)

Question

I have a website where users can comment on photos. I have a table of comments in this format:

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(12) | NO   |     | NULL    |       |
| id       | varchar(32) | NO   | MUL | NULL    |       |
| whenadd  | int(20)     | NO   |     | NULL    |       |
| text     | text        | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

And a table of photos in this format: (JUST TO CLARIFY, ID refers to ID in the photo table, this is abbreviated)

+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| id       | varchar(32)      | NO   | PRI | NULL    |       |
| type     | varchar(5)       | NO   |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+

So basically, I'm trying to return the photos that have been commented, in the date in which they were commented, ordered by the most recent comment on each photo. I have tried using INNER JOIN to do it, but it never seems to work right. Any ideas?

Answer 1

SELECT * FROM comments LEFT JOIN photos ON comments.ID=photos.ID WHERE comments.ID=PHOTOID ORDER BY whenadd DESC

change PHOTOID to your photo ID

Answer 2

select p.id, p.type, max(c.whenadd) from photos p, comments c where p.id = c.photo_id group by photo.id order by c.whenadd desc - this will return only commented photos. Use left join to return all photos. But it seems that you don't have foreign key between these two tables. Table comments should have a column photo_id (or something similar) to refer to the corresponding photo in photo table.

Answer 3

The question's a little unclear to me - photos that have been commented in the date they were commented - this seems like every photo. Feel free to clarify that.

If you want photos sorted by the most recent coment, I'd do:

SELECT
  photo.id,
  MAX(c.whenadd) as last -- and maybe other fileds as well --
FROM
  photos photo LEFT JOIN comments c ON c.id=photo.id
GROUP BY
  photo.id
ORDER BY last DESC

Note that there is a problem with photos that have no comments - they will be sorted last (I think), because MAX(empty set) is NULL.

If you want photos that were commented on some special date, sorted by their last comment, you just add another join:

SELECT
  photo.id,
  MAX(c.whenadd) as last -- and maybe other fileds as well --
FROM
  photos photo NATURAL JOIN comments c NATURAL JOIN comments c2
WHERE
  c2.whenadd = yourspecialdate
GROUP BY
  photo.id
ORDER BY last DESC

Answer 4

it is better to add lastCommentDate column to the photos table and populate it each time someone leave comment.

It will perform faster rather than load such query every time.