Working in a MySQL database, I have tables set up like this:
Table song_instances has song_id (int), meeting_instance_id (int)
The meeting_instances table has columns meeting_instance_id (int), meeting_date (datetime), meeting_id (int).
Each meeting_id
(referencing the meetings table) might have numerous meeting_instances, and each meeting_instance might have numerous songs associated with it.
I'm trying to get a list of all songs from song_instances
that are related to a particular meeting type (meeting_id
)and include the date of the most recent date that the song has been used. Because the meeting_id and the meeting_date are only referenced in the meeting_instances table, I am joining that table in. But every attempt falls short.
Example: song_instances:
song_id meeting_instance_id 1 1 1 2 2 2 2 3 3 1 1 5 3 5
Meeting Instances:
meeting_instance_id meeting_id meeting_date 1 4 2014-09-01 2 4 2014-09-02 3 2 2014-09-03 5 4 2014-09-04
I want to get back:
song_id lastDate 1 2014-09-04 2 2014-09-03 3 2014-09-04
etc.
Here is my most recent (and closest to successful) attempt.
SELECT si.song_id, mi.lastDate FROM song_instances si LEFT JOIN
(SELECT MAX(meeting_date) as lastDate, meeting_id, meeting_instance_id FROM meeting_instances WHERE meeting_id = 4 GROUP BY meeting_instance_id) as mi
ON si.meeting_instance_id = mi.meeting_instance_id
GROUP BY si.song_id ORDER BY lastDate DESC
This currently gives me the date farthest in the past rather than the most recent date. Not sure why. Any thoughts on how to improve this query?
This is a derived table from a much larger query, that is selecting song names and topics from other tables, but this is the only part of the whole query that is not working as intended.