MariaDB 10.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `events` (
`ID`  int NOT NULL,
`Name`  varchar(255) NULL,
PRIMARY KEY (`ID`)
)
;
CREATE TABLE `events_meta` (
`ID`  int NOT NULL,
`EventID`  int NOT NULL,
`MetaKey`  varchar(255) NULL,
`MetaValue`  varchar(255) NULL,
PRIMARY KEY (`ID`)
)
;
INSERT INTO `events` (`ID`, `Name`) VALUES ('1', 'First event');
INSERT INTO `events` (`ID`, `Name`) VALUES ('2', 'Second event');
INSERT INTO `events` (`ID`, `Name`) VALUES ('3', 'Third event');
INSERT INTO `events` (`ID`, `Name`) VALUES ('4', 'Fourth event');
INSERT INTO `events` (`ID`, `Name`) VALUES ('5', 'Fifth event');
INSERT INTO `events_meta` (`ID`, `EventId`, `MetaKey`, `MetaValue`) VALUES ('1', '2', 'date_expired', '2023-02-09 00:00:00');
INSERT INTO `events_meta` (`ID`, `EventId`, `MetaKey`, `MetaValue`) VALUES ('2', '2', 'date_expired', '2023-01-23 00:00:00');
INSERT INTO `events_meta` (`ID`, `EventId`, `MetaKey`, `MetaValue`) VALUES ('3', '3', 'date_expired', '2023-04-30 00:00:00');
INSERT INTO `events_meta` (`ID`, `EventId`, `MetaKey`, `MetaValue`) VALUES ('4', '3', 'date_expired', '2023-01-22 00:00:00');
INSERT INTO `events_meta` (`ID`, `EventId`, `MetaKey`, `MetaValue`) VALUES ('5', '4', 'date', '2023-06-12 00:00:00');
INSERT INTO `events_meta` (`ID`, `EventId`, `MetaKey`, `MetaValue`) VALUES ('6', '4', 'date', '2023-02-03 00:00:00');
INSERT INTO `events_meta` (`ID`, `EventId`, `MetaKey`, `MetaValue`) VALUES ('7', '5', 'date_expired', '2023-02-13 00:00:00');
INSERT INTO `events_meta` (`ID`, `EventId`, `MetaKey`, `MetaValue`) VALUES ('8', '5', 'date', '2023-01-02 00:00:00');
INSERT INTO `events_meta` (`ID`, `EventId`, `MetaKey`, `MetaValue`) VALUES ('9', '5', 'date', '2023-01-01 00:00:00');
SELECT events.ID, events.Name, events_meta.MetaKey, events_meta.MetaValue FROM `events` LEFT JOIN `events_meta` ON events.ID = events_meta.EventId ORDER BY events.ID;


ID
Name
MetaKey
MetaValue


1
First event
null
null

2
Second event
date_expired
2023-02-09 00:00:00

2
Second event
date_expired
2023-01-23 00:00:00

3
Third event
date_expired
2023-04-30 00:00:00

3
Third event
date_expired
2023-01-22 00:00:00

4
Fourth event
date
2023-06-12 00:00:00

4
Fourth event
date
2023-02-03 00:00:00

5
Fifth event
date_expired
2023-02-13 00:00:00

5
Fifth event
date
2023-01-02 00:00:00

5
Fifth event
date
2023-01-01 00:00:00


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;


Name


Fourth event

Fifth event

Second event

Third event

First event

ID	Name	MetaKey	MetaValue
1	First event	null	null
2	Second event	date_expired	2023-02-09 00:00:00
2	Second event	date_expired	2023-01-23 00:00:00
3	Third event	date_expired	2023-04-30 00:00:00
3	Third event	date_expired	2023-01-22 00:00:00
4	Fourth event	date	2023-06-12 00:00:00
4	Fourth event	date	2023-02-03 00:00:00
5	Fifth event	date_expired	2023-02-13 00:00:00
5	Fifth event	date	2023-01-02 00:00:00
5	Fifth event	date	2023-01-01 00:00:00