Limiting SQL LIKE to same wildcard in AND parameter

Question

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

Your WHERE condition can never be true. meta_key can't be like date_%_start and date_%_end in the same row. — Barmar
– Barmar, Commented Aug 8, 2022 at 20:57
I tried to simplify the query and it went wrong, so I made a sqlfiddle with the actual one: sqlfiddle.com/#!9/fb497e/2 — Lucius
– Lucius, Commented Aug 8, 2022 at 21:18

Barmar · Accepted Answer · 2022-08-08 21:04:21Z

2

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.

answered Aug 8, 2022 at 21:04

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lucius Over a year ago

Awesome! Just a question: adding the SUBSTRING_INDEX condition as another AND at the end of the query also works. Is there any difference from how you made it? Example: sqlfiddle.com/#!9/fb497e/7

Barmar Over a year ago

There's generally no difference between putting these conditions in ON or WHERE (left join is an exception). By convention we put conditions that relate the tables in ON, conditions on a single table go in WHERE.

Collectives™ on Stack Overflow

Limiting SQL LIKE to same wildcard in AND parameter

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related