I'm searching for a way to create a query to do the following:
Let's consider 3 tables:
- products: List of products
- tags: list of tags
- tag_ties: table used to associate a tag to a product
Let's consider this structure for each table:
Products:
- id (int, autoincrement)
- name (varchar, name of the product)
Tags:
- id (int autoincrement)
- label (varchar, label of the tag)
Tag_ties:
- id (int, autoincrement)
- tag_id (int, reference to a tag id)
- ref_id (int, reference to a product id)
What I want:
Obtain all the products who are tagged with tags id 10, 11 and 12 for example.
This query does not work, as it returns the products having at least one of the tags:
select
p.name as name,
p.id as id
from
products p inner join tag_ties ties
on
p.id=ties.ref_id
where
ties.ref_id=p.id and
ties.tag_id in (10,11,12)
group by
p.id
order by
p.name asc
Best Answer
Try something like this: