Get posts by category with pure SQL query

I have a meeting post type, whose titles automatically get set to a date of the form m/d/Y. I have the below query which gets all unique years from the post titles by selecting distinct values after the last / inside the post title.

SELECT DISTINCT substr(post_title, - instr(reverse(post_title), "https://wordpress.stackexchange.com/") + 1)
AS year FROM {$wpdb->posts}
WHERE post_status="publish"
AND post_type="meeting"

This outputs an array looking something like ["2019", "2018", "2017"].

What I want is to refine this down further to be able to consider posts of only a certain category.

Now I know the below example is wrong, since categories are not actually stored on the posts themselves, but basically I want to refine by post category like this:

SELECT DISTINCT substr(post_title, - instr(reverse(post_title), "https://wordpress.stackexchange.com/") + 1)
AS year FROM {$wpdb->posts}
WHERE post_status="publish"
AND post_type="meeting"
AND post_category = 'some_cat_slug'

This query is about the extent of my SQL knowledge so I’m not sure how to do the join in order to filter by category.

Edit: To clarify, this is just for generating pagination buttons by year. So in short, I am trying to find all years that have at least one meeting post for a specific category. If this can be done with WP_Query only, I’d be happy to hear it.

Edit 2: I’ve solved my issue by storing the post categories in the title as well. I’ll leave this here if anyone else wants to answer though.

If you want to be able to get the category by name, this should work:

SELECT DISTINCT substr(post_title, - instr(reverse(post_title), "https://wordpress.stackexchange.com/") + 1)
AS year FROM {$wpdb->posts} p
LEFT JOIN {$wpdb->term_relationships} rel ON rel.object_id = p.ID
LEFT JOIN {$wpdb->term_taxonomy} tax ON tax.term_taxonomy_id = rel.term_taxonomy_id
LEFT JOIN {$wpdb->terms} t ON t.term_id = tax.term_id
WHERE post_status="publish"
AND post_type="meeting"
AND t.name="Category Name"
AND tax.taxonomy = 'category'

If you know the term_taxonomy_id of your category (mostly, but not always, the term_id), you can accomplish this with fewer left joins like this:

SELECT DISTINCT substr(post_title, - instr(reverse(post_title), "https://wordpress.stackexchange.com/") + 1)
AS year FROM {$wpdb->posts} p
LEFT JOIN {$wpdb->term_relationships} rel ON rel.object_id = p.ID
WHERE post_status="publish"
AND post_type="meeting"
AND rel.term_taxonomy_id = <term_taxonomy_id of your category>

Happy Coding!