Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

3 Answers

up vote 8 down vote accepted

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
;
share|improve this answer
    
It's working :) –  Julien L Nov 5 '11 at 3:07
add comment

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 ...
share|improve this answer
    
Thanks, even more useful and simple! –  Julien L Nov 6 '11 at 18:46
add comment

The subquery from the selected answer isn't needed. To select products with all the given tag ids the query can be simply:

SELECT 
    p.*
FROM 
    products AS p
INNER JOIN
    tag_ties AS tt
ON
    tt.ref_id = p.id
AND 
    tt.tag_id IN (10, 11, 12)
GROUP BY 
    p.id
HAVING 
    COUNT(p.id)=3

Extending this idea, we can also query based on the tag labels in a single shot. To select products with the tags ('foo', 'bar', 'baz'):

SELECT 
    p.*
FROM 
    products AS p
INNER JOIN
    tags AS t
ON
    t.label IN ('foo', 'bar', 'baz')
INNER JOIN
    tag_ties AS tt
ON
    tt.ref_id = p.id
AND 
    tt.tag_id = t.id
GROUP BY 
    p.id
HAVING 
    COUNT(p.id)=3

To complicate it a bit, we can use a subquery to mix intersection (AND) and union (OR). The query below will return products with all the tags of the group ('foo', 'bar') and at least one of the tags of the group ('baz', 'ding'):

SELECT 
    p.*
FROM 
    (
    SELECT 
        p.*
    FROM 
        products AS p
    INNER JOIN 
        tags AS t
    ON
        t.label IN ('foo', 'bar')
    INNER JOIN 
        tag_ties AS tt
    ON
        tt.ref_id = p.id
    AND 
        tt.tag_id = t.id
    GROUP BY 
        p.id
    HAVING 
        COUNT(p.id)=2
    ) AS p
INNER JOIN 
    tags AS t
ON 
    t.label IN ('baz', 'ding')
INNER JOIN
    tag_ties AS tt
ON
    tt.ref_id = p.id
AND 
    tt.tag_id = t.id
GROUP BY 
    p.id
share|improve this answer
2  
You don't need a JOIN? No, technically you don't but is there any reason for not using it? And going back to SQL-89 notation of implicit joins? –  ypercube Apr 18 '13 at 10:57
4  
I'm downvoting because you should use JOIN always. stackoverflow.com/questions/5654278/… Without an explicit JOIN, your code would not be deployed in my shop –  gbn Apr 18 '13 at 12:04
2  
Hey guys, thanks for telling me that implicit joins are bad style. I mainly intended to point out that the subquery from the selected answer was not necessary. I edited the answer to use joins. If you see anything else wrong in my queries please let me know. –  moraes Apr 18 '13 at 14:13
4  
+1 for taking the downvote without getting upset and actually taking the advice into consideration for improving your skills. –  Zane Apr 18 '13 at 14:20
2  
What @Zane said. +1 too –  gbn Apr 18 '13 at 14:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.