SQLITE: A problem of tags and products

querysqlitesubquery

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:

select
    t1.id,
    t1.name
from
    (
    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.tag_id in (10,11,12)
    ) as t1
group by
    t1.id,
    t1.name
having
    count(t1.id) = 3
order by
    t1.name asc
;
Related Question