I have two tables:
I would like to select all items from events table ordered by the following logic:
- First "group" of items on the list are the ones that have any "date" rows in events_meta table.
- Second "group" of items on the list are the ones that have any "date_expired" rows in events_meta table.
- Third "group" of items on the list are the ones that don't have "date" nor "date_expired" rows.
Also, within each of those groups (except the last one) events are ordered by their "date" or "date_expired" minimum date values. So the first group is ordered by "date" dates. Second group is ordered by "date_expired" dates.
Each event can have any number of "date" or "date_expired" rows attached to them.
The desired outcome would be:
- Fifth event
- Fourth event
- Third event
- Second event
- First event
I think I have managed to create the "groups" but I'm struggling to sort the groups by dates:
SELECT events.Name FROM `events` LEFT JOIN `events_meta` ON events.ID = events_meta.EventId GROUP BY events.Name ORDER BY CASE WHEN (SELECT COUNT(*) FROM events_meta WHERE events.ID = events_meta.EventID AND events_meta.MetaKey = 'date') > 0 THEN 0 WHEN (SELECT COUNT(*) FROM events_meta WHERE events.ID = events_meta.EventID AND events_meta.MetaKey = 'date_expired' AND events_meta.MetaKey != 'date') > 0 THEN 1 ELSE 2 END;
Here's a dbfiddle with the exact same dataset: https://dbfiddle.uk/55vp__vZ