MySQL - multiple CASE ordering with joined table

2 votes
1 answer
120 views
                          I have two tables:

**EVENTS**

    
  | ID | Name         |
  |----|--------------|
  |  1 | First event  |
  |  2 | Second event |
  |  3 | Third event  |
  |  4 | Fourth event |
  |  5 | Fifth event  |
    

**EVENTS_META**

    
  | ID | EventID | MetaKey      | MetaValue           |
  |----|---------|--------------|---------------------|
  |  1 |       2 | date_expired | 2023-02-09 00:00:00 |
  |  2 |       2 | date_expired | 2023-01-23 00:00:00 |
  |  3 |       3 | date_expired | 2023-04-30 00:00:00 |
  |  4 |       3 | date_expired | 2023-01-22 00:00:00 |
  |  5 |       4 | date         | 2023-06-12 00:00:00 |
  |  6 |       4 | date         | 2023-02-03 00:00:00 |
  |  7 |       5 | date_expired | 2023-02-13 00:00:00 |
  |  8 |       5 | date         | 2023-01-02 00:00:00 |
  |  9 |       5 | date         | 2023-01-01 00:00:00 |
    


I would like to select all items from events table ordered by the following logic:
1. First "group" of items on the list are the ones that have any "date" rows in events_meta table.
2. Second "group" of items on the list are the ones that have any "date_expired" rows in events_meta table.
3. 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:**

1. Fifth event
2. Fourth event
3. Third event
4. Second event
5. 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 
                        
Asked by Martin J (23 rep)
Mar 30, 2023, 03:41 PM
Last activity: Mar 31, 2023, 05:15 AM
MySQL - multiple CASE ordering with joined table

Related Questions
