MySQL – Select Records Based on Earliest Date in Linked Record

datedenormalizationjoin;MySQL

I am a retired teacher working on my first MYSQL database as a personal project; please let me know if I've inadvertently violated conventions. And thank you!

The database's domain is fiction stories, and I have these tables (among others):

Stories: | story_id | title | genre | …

Venues : | venue_id | name | year | …

Story_in_Venue: | story_id | venue_id |

The Story_in_Venue table implements a many-to-many relationship, indicating that a given story appeared in a particular venue (such as a magazine or anthology). One of the common searches is for all stories within a given genre (say 'mystery') in which the earliest venue is in a particular year (say 1960).

If I made a generated column in the Stories table to hold the year of a story's earliest venue, then the search would be quick and easy, going straight to the stories I need (provided that I have an appropriate composite index). But it's been drilled into me to not duplicate information that's already available in another table.

Is there a good way to solve this problem:

  1. without that earliest year column in the Stories table (and no other generated or trigger-constrained column either), and
  2. without having to examine all mystery stories or a bunch of irrelevant venues.

Many thanks for sharing your knowledge on this beginner's question.

–Michael

Best Answer

Good question. First you need the full date rather than just a year to pick the earliest in year. So I'm assuming event_date in Venues

SELECT title, name, MIN(event_date)
FROM Stories s
JOIN Story_in_Venue sv ON s.story_id = sv.story_id
JOIN Venues ON v.venue_id = sv.venue_id
WHERE genre="Mystery" and event_date >= "1960-01-01" AND event_date < "1961-01-01"
GROUP BY s.story_id

The event_date and genre are the searched columns so they should be indexes/

Related Question