CODEWITHSUNDEEP
mysqlsql
beauchette
beauchette

Reputation: 1124

Select on multiple different values of the same field in a many to many relation

I'm not the sure the title is really helpful, so here is my problem, here are three tables :

cats :

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

tags :

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

cats_tags:

+--------+---------+------+-----+---------+----------------+
| Field  | Type    | Null | Key | Default | Extra          |
+--------+---------+------+-----+---------+----------------+
| id     | int(11) | NO   | PRI | NULL    | auto_increment |
| id_cat | int(11) | NO   |     | NULL    |                |
| id_tag | int(11) | NO   |     | NULL    |                |
+--------+---------+------+-----+---------+----------------+

let's imagine that the tables are populated by 10 cats, and 5 tags (id 1 to 5) and that those cats are tagged with some random tags each.

how can I select all cats that have an entire list of tags ?

for example how can I list cats with tags 2 and 3 (it could be 1,2 and 3, or 2,3 and 4) but not cats who only have 2 or 3 or none of those two tags ?

I'm not sure it's standard sql, I'd want to do that with mysql.

EDIT:

I tried that :

select
    c.id, c.name
from
    cats c
inner join cats_tags r on c.id = r.id_cat
where
    r.id_tag in (:tags)
group by c.id

unitl I remembered that in would take any cat that has at least of the :tags list I asked for. and I only want cats that has all the tags I gave.

Upvotes: 1

Views: 75

Answers (2)

John Bollinger
John Bollinger

Reputation: 181104

You might approach the problem like this:

SELECT cat_id
FROM (
    SELECT DISTINCT cat_id, tag_id
    FROM cat_tags
    WHERE tag_id IN (2,3)
  ) tag23
GROUP BY cat_id
HAVING COUNT(*) = 2

The inline view is required because nothing you have shown prevents the same (cat, tag) pairing from appearing multiple times in cat_tags; the query could be expressed more simply if it could be assumed (because of a DB constraint) that such duplication would not occur.

You can join cat to the result if you need to get cat names.

Upvotes: 2

void
void

Reputation: 7890

use where exists...subquery:

select distinct c.* 
from cat c 
where exists
(  select 1 from cats_tags ct
   join tags t on ct.id_tag=t.id
   where t.id=2 and c.id=ct.id_cat
)
and exists
(  select 1 from cats_tags ct
   join tags t on ct.id_tag=t.id
   where t.id=3 and c.id=ct.id_cat
)

Upvotes: 0

Related Questions