Ok, I have a database where I am storing TV shows and I want users to be able to specify a decade and get all item that aired during that decade.
SELECT m.id,
(SELECT debut_date FROM tv_release_info as date WHERE date.tv_ref = m.id ORDER BY date.creation_date DESC LIMIT 1) as reldate,
(SELECT title FROM tv_title as md WHERE md.tv_ref = m.id ORDER BY md.creation_date DESC LIMIT 1) as item_title,
(SELECT overview FROM shared_overview as ov WHERE ov.parent_ref = m.id AND ov.type = ‘tv’ ORDER BY ov.creation_date DESC LIMIT 1) as overview
FROM tv as m
INNER JOIN tv_release_info as date
ON date.tv_ref = m.id
AND year( date.debut_date ) between 1980 and 1989
I am not the most versed in MYSQL and this one has me pretty perplexed. It works fine for things like movies that only have a release date but I am not sure even where to start on making it work as a range. I tried doing this “AND year( date.debut_date ) between 1980 and 1989 OR year( date.end_date ) between 1980 and 1989” And it kind of works but if something aired from 70’s - 90’s and the user enters 80’s as the decade sort it will not return it.
Edit Forgot to mention the date is stored as a mysql datetime