Jump to content
  • Advertisement
Sign in to follow this  

[web] MySQL Query issue

This topic is 3856 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
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

Participate in the game development conversation and more when you create an account on GameDev.net!

Sign me up!