• Advertisement
Sign in to follow this  

[web] MySQL Query issue

This topic is 3766 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hey Guys Ive got 2 tables, one containing a list of images, and one containing all the moderation actions contained on these images. These tables are called "images" and "journal_mod_log" respectively. My aim is to create a list each images most recent moderation action. The problem im having is this..... Initially I tried just grabbing the data out using the query below, which produced a list of non distinct values... select images.image_title, journal_mod_log.reason, journal_mod_log.timestamp from images join journal_mod_log on images.image_id = journal_mod_log.image_id; the result of which was this... I thought "thats ok, I'll just use a group by clause for images.image_id, and use max to get the most recent timestamp, thus getting the most recent moderation action. Or so I thought. The query I used was this (which produced the following result) select images.image_title, journal_mod_log.reason, max(journal_mod_log.timestamp) from images join journal_mod_log on images.image_id = journal_mod_log.image_id group by(images.image_id); Essentially the group by clause seems to be taking the most first instance stored of image_id, which I thought I could overcome by specifying to select the maximum value of timestamp for that image_id, but that doesnt seem to be the case. Does anyone know how to overcome this?

Share this post


Link to post
Share on other sites
Advertisement
This is a bit complex, but the query is:

select journal_mod_log.timestamp, images.image_title, journal_mod_log.reason
from journal_mod_log join images on images.image_id = journal_mod_log.image_id
where journal_mod_log.timestamp =
(select max(journal_mod_log.timestamp)
from journal_mod_log
where journal_mod_log.image_id = images.image_id);

Skizz

Share this post


Link to post
Share on other sites
Quote:
Original post by Skizz
This is a bit complex, but the query is:

select journal_mod_log.timestamp, images.image_title, journal_mod_log.reason
from journal_mod_log join images on images.image_id = journal_mod_log.image_id
where journal_mod_log.timestamp =
(select max(journal_mod_log.timestamp)
from journal_mod_log
where journal_mod_log.image_id = images.image_id);

Skizz



Skizz, I could hug you!

Cheers :)

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement