SQLITE: A problem of tags and products

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

You can solve this problem using intersect statements. Do a separate select for each tag_id and join them with intersects and you'll only get the records that match all three tag_ids.

select products.id, products.name from 
products join tag_ties
on tag_ties.ref_id = products.id
where tag_ties.tag_id = 10
intersect
select products.id, products.name from 
products join tag_ties
on tag_ties.ref_id = products.id 
where tag_ties.tag_id = 11
intersect
select products.id, products.name from 
products join tag_ties
on tag_ties.ref_id = products.id 
where tag_ties.tag_id = 12

Here's a reference article on using intersect

You can also use a temporary view to make this look a little nicer.

create temporary view temp_view as 
select name, products.id as id, tag_ties.tag_id as tag_id 
from products join tag_ties
on tag_ties.ref_id = products.id

select name, id from temp_view where tag_id = 10
intersect ...

StackExchange Database Administrators Q#7603, answer score: 12