CODEWITHSUNDEEP
mysqlselecttags
Idan Ptichi
Idan Ptichi

Reputation: 316

Select items by including & excluding their tags

Consider the following schema:

id | question
-----------
1  | How old are you?
2  | Are you a male or female?

question_tag:

id | question_id | tag_id
--------------------------
1  | 1       | 1
2  | 1       | 2
3  | 1       | 3
4  | 2       | 1
5  | 2       | 2

tags:

id | tag
--------------
1  | some_tag
2  | some_other_tag
3  | different_tag

question and tag have many to many relationship.

I would like to select all the questions which have certain tags and haven't some other tags.

e.g. question that has both tag ids [1,2] but hasn't tag id [3]

What i have currently is:

select `questions`.`id`,
       `questions`.`question`,
from `questions`
       inner join `question_tag` on `questions`.`id` = `question_tag`.`question_id`
  where `tag_id` in (1,2)
group by `questions`.`id`, `questions`.`question`
having count(tag_id) = 2;

The 'include' part works - I get only the questions which has both the tags.

Now I need to add the 'exclude' part - e.g. filter our questions which also have tag_id = 3.

I tried adding AND tag_id NOT IN (3) without success - those questions were not filtered out from the results.

Any idea what am I missing?

Upvotes: 1

Views: 56

Answers (1)

Fahmi
Fahmi

Reputation: 37473

You can try using correlated subquery with not exists

select `questions`.`id`,
       `questions`.`question`,
from `questions`
       inner join `question_tag` on `questions`.`id` = `question_tag`.`question_id`
  where  `tag_id` in (1,2) and not exists (select 1 from question_tag x where `question_tag`.`question_id`=x.`question_id` and x.tag_id=3)
group by `questions`.`id`, `questions`.`question`

Upvotes: 1

Related Questions