Limiting SQL LIKE to same wildcard in AND parameter-CodePudding

I'm trying to create a SQL query that retrieves records based on the start and end dates. However, the system doesn't have a very nice Database schema, so I'm looking for a way to create a query that works with it.

Basically, there's a meta table that stores the events' dates using the "date_NUMBER_(start|end)" format:

event_id	meta_key	meta_value
1	date_0_start	2022-04-02
1	date_0_end	2022-04-03
-	-	-
2	date_0_start	2022-03-21
2	date_0_end	2022-03-22
2	date_1_start	2022-06-24
2	date_1_end	2022-06-30

So, Event 1 has one date span: 2022-04-02 to 2022-04-03. While Event 2 has two: 2022-03-21 to 2022-03-22 and 2022-06-24 to 2022-06-30.

After 2022-03-22, Event 2 is not active, starting again only on 2022-06-24.

To create a filter that works with this schema, I came up with the following SQL query. For example, to search for events happening between the 2022-04-01 - 2022-04-03 range (any event that there's an event day between the range):

SELECT events.id
FROM events INNER JOIN events_meta ON ( events.id = events_meta.event_id ) INNER JOIN events_meta AS evt1 ON (
events.id = evt1.event_id )
WHERE 

(
( 
    events_meta.meta_key LIKE 'date_%_start' AND
    CAST(events_meta.meta_value AS DATE) >= '2022-04-01'
)
AND
(
    evt1.meta_key LIKE 'date_%_end' AND
    CAST(evt1.meta_value AS DATE) <= '2022-04-03'
)

)

The problem is that in this way, I should get only Event 1, but Event 2 is also returned since date_1_start is >= '2022-04-01' and date_0_end is <= '2022-04-03'

I'm not being able to find a way where I can match the NUMBER in the "date_NUMBER_(start|end)" meta_key format, so the query doesn't compare different NUMBERs.

Any help is appreciated :)

I made a fiddle with the INNER JOIN query:

http://sqlfiddle.com/#!9/fb497e/2

CodePudding user response：

Use a self-join to get different keys for the same event ID. See MYSQL Select from tables based on multiple rows

SELECT m1.event_id, m1.meta_value AS start, m2.meta_value AS end
FROM events_meta AS m1
JOIN events_meta AS m2 
    ON m1.event_id = m2.event_id 
        AND SUBSTRING_INDEX(m1.meta_key, '_', 2) = SUBSTRING_INDEX(m2.meta_key, '_', 2)
WHERE m1.meta_key LIKE 'date_%_start' AND m2.meta_key LIKE 'date_%_end'
    AND CAST(m1.meta_value AS DATE) >= '2022-04-01'
    AND CAST(m2.meta_value AS DATE) <= '2022-04-03'

The SUBSTRING_INDEX() calls will return the prefixes date_0, date_1, etc. Including this in the ON condition will pair up the corresponding start and end keys.