Many to many selection of news by tags?

Question

MySQL

Good day, there are 3 tables
news
id|text

tags
id|name

news_tags
id|tag_id|news_id

you need to collect news by tags, main tag + additional tag, for example, select news with the tag "animals" and "moles", tag "moles" may or may not be present (that is, the entire entry with the tag "animals" and , "animals" and "moles" in a pair), there is a solution through UNION, but I think it is redundant here.

UPD probably asked the question a little wrong, it will probably be clearer using an example
if we set a selection of 2 tags "animals" "moles" there should be news
where the tag is only "animals", where the tags are a pair of "animals" "moles",

something like this, but I think this is not the best solution

SELECT news_id FROM news_tags WHERE tag_id = "id тега животное" OR tag_id = "id тега кроты" GROUP BY news_id HAVING COUNT(news_id) = 2; --находим пару животное кроты
UNION
SELECT news_id FROM news_tags WHERE tag_id = "id тега животное" AND news_id not in (SELECT news_id FROM news_tags WHERE tag_id in ("все теги в системе")); -- находим все новости с тегом животные и только ним больше у новости не может быть другого тега.

Answer 1

You understand that according to your logic, the additional tag does not play any role, because if the selection is needed with and without "moles", it's just all "animals". And if you need to search strictly by two tags, then here:

SELECT n.*
FROM news AS n
JOIN news_tags AS nt ON nt.news_id = n.id
JOIN tags AS t1 ON t1.id = nt.tag_id AND t1.name = "животные"
JOIN tags AS t2 ON t2.id = nt.tag_id AND t2.name = "кроты"

Here is a link to a fiddle with an example
www.sqlfiddle.com/#!9/70ce50/2/0

Answer 2

It makes no sense to add a table with each new tag, you can use in and having

SELECT n.id, n.text
FROM news AS n
JOIN news_tags AS nt ON nt.news_id = n.id
JOIN tags AS t1 ON t1.id = nt.tag_id AND t1.name in ("животные", "кроты" /*перечисляете свои теги*/)
group by n.id, n.text
having count(distinct t1.id) = 2 /*кол-во перечисленных тегов*/