data scheme is as follows

Create table post (id, name) Create table post_to_tag (post_id, tag_id) Create table tag (id, name)

It is necessary to write a query that extracts all objects A (posts) to which objects B (tags) are attached with the values ​​'tag1' and 'tag2' (at the same time, the intersection is important). But at the same time from the sample you need to remove posts that have tags with the values ​​of the name 'tag3' and 'tag4'

The intersection of the tags present is extracted like this:

 SELECT p.id, GROUP_CONCAT(DISTINCT all_tags.name) as i_tag from post as p INNER JOIN post_to_tag AS ptg1 ON ptg.post_id = p.id INNER JOIN tag AS t1 ON ptg1.tag_id = t1.id and t1.name = 'тэг1' INNER JOIN post_to_tag AS ptg2 ON ptg.post_id = p.id INNER JOIN tag AS t1 ON ptg2.tag_id = t2.id and t2.name = 'тэг2' INNER JOIN post_to_tag AS all_ptg ON all_ptg.post_id = p.id INNER JOIN tag AS all_tags ON all_git.tag_id = all_tags.id GROUP BY (i.id)

Tell me, how can you filter by missing ???

    1 answer 1

    If absolutely in the forehead, then something like this:

     select p.id, GROUP_CONCAT(DISTINCT all_tags.name) as i_tag from post as p INNER JOIN post_to_tag AS all_ptg ON all_ptg.post_id = p.id INNER JOIN tag AS all_tags ON all_git.tag_id = all_tags.id GROUP BY i.id HAVING sum(if(all_tags.name in('тэг1','тэг2'),1,0)) = 2 AND sum(if(all_tags.name in('тэг3','тэг4'),1,0)) < 2

    Here we count the number of tags with the names we need and check whether all were. But it’s not clear whether you can have several identical tags in one post (this will bring down the counting) and this may not be effective if the indices are built correctly, there is a lot of data and tags 1 and 2 are few and would be faster to find them by index. .

    Or remake the IN / NOT IN request:

     select p.id, GROUP_CONCAT(DISTINCT all_tags.name) as i_tag from post as p INNER JOIN post_to_tag AS all_ptg ON all_ptg.post_id = p.id INNER JOIN tag AS all_tags ON all_git.tag_id = all_tags.id WHERE p.id IN( select ptg.post_id from post_to_tag ptg INNER JOIN tag AS t1 ON ptg.tag_id = t1.id and t1.name in('тэг1','тэг2') GROUP BY ptg.post_id HAVING count(distinct t1.name)=2 ) AND p.id NOT IN( select ptg.post_id from post_to_tag ptg INNER JOIN tag AS t1 ON ptg.tag_id = t1.id and t1.name in('тэг3','тэг4') GROUP BY ptg.post_id HAVING count(distinct t1.name)=2 ) GROUP BY i.id

    It is also possible to combine parts from these requests among themselves and with your initial variant, to see at which of them the speed will be higher.

    • Thank! If in the frontal solution in the last condition to correct "<2" to "= 0", then it will work as expected. - Roosh 4:21 pm
    • @Roosh Then you can, having with this condition, add to your original request, all the same there are more chances for normal performance - Mike