I’m looking for the best way to handle the following query. I have a table of items, some (but not all) of which are part of a series of items (one per day). The ones in a series are grouped by a shared series ID:
Table "items": id series_id (may be NULL) title date etc.
The difficulty arises when I sometimes only want to return the next upcoming instance of any items which are part of a series. Something like:
SELECT items.id, items.series_id, items.title, items.date FROM items LEFT JOIN items AS series_table ON series_table.series_id IS NOT NULL AND series_table.series_id=items.series_id AND series_table.date>=CURDATE() WHERE items.date>=CURDATE() GROUP BY items.id HAVING (items.series_id IS NULL OR items.date=MIN(series_table.date));
This seems to be inefficient. Doing it with a subquery in the WHERE clause also doesn’t perform that well on my data set. Is there a better way to do this? I realize it might make sense to perform two queries with a UNION but unfortunately that’s the one thing that the application executing this query would make difficult, so I’m hoping there’s a better single-query approach.
CREATE TABLE items ( id int(11) unsigned NOT NULL AUTO_INCREMENT, title varchar(30) NOT NULL DEFAULT '', series_id int(10) DEFAULT NULL, date datetime NOT NULL, PRIMARY KEY (id), KEY series_id (series_id) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; INSERT INTO items (id, title, series_id, date) VALUES (1, 'Item 1 (no series)', NULL, '2019-12-01 00:00:00'), (2, 'Item 2 (no series)', NULL, '2019-12-02 00:00:00'), (3, 'Item 3 (series)', 1, '2019-12-03 00:00:00'), (4, 'Item 4 (series)', 1, '2019-12-04 00:00:00');