CODEWITHSUNDEEP
mysqlsqljoinexists
admash
admash

Reputation: 45

Selecting rows that are included in a set from another table

I have a table "Products" with a product name and id:

id    |  title
1        product 1
2        product 2

Each product can have a series of tags. Tags are identified in table "Attributes":

id |   name     | handle
1      Tag One    tag-one
2      Tag Two    tag-two
3      Tag Three  tag-three
4      Tag Four   tag-four
etc

The product to tag relationship is another table "Tags":

id  | AttId | OwnerId
1       1        1
2       2        1
3       1        2
4       3        2
etc

Ok, so I am trying to select a set of products that all have at least one specific tag, and a possible selection of other tags. Here is what I am working with now:

SELECT products.id
FROM products
WHERE
EXISTS
( 
    SELECT  1
    FROM Tags
    INNER JOIN Attributes ON tags.AttId = Attributes.id
    WHERE Attributes.handle = 'tag-one'
    AND (
            Attributes.handle = 'tag-two'
            OR
            Attributes.handle = 'tag-four'
        )

    AND products.id = Tags.OwnerId
)

If I remove the AND (OR) section, the query works. As above, it shows no errors, but also no results; How should I write this so I can get a set of products that have one tag for sure, AND have either/or other specified tags by the tag handle?

Upvotes: 0

Views: 58

Answers (2)

cneff
cneff

Reputation: 398

I think if you perform two separate queries and take the intersection, that will give you what you want.

-- Get all the owner ids that have 'tag-one'
select OwnerId
from Tags t1
where AttId in
(
  select id
  from Attributes a1
  where a1.handle = 'tag-one'
)
intersect
-- Get all the owner ids that have tag-two and tag-four
select OwnerId
from Tags t2
where AttId in
(
  select id
  from Attributes a2
  where a2.handle in ('tag-two', 'tag-four')
)
;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270051

I like to approach this type of problem using group by and having -- because I find that this method works very well for expressing many different conditions. For your conditions:

select p.*
from products p join
     tags t
     on t.OwnerId = p.id join
     attributes a
     on t.attid = a.id
group by p.id
having sum(a.handle = 'tag-one') > 0 and
       sum(a.handle in ('tag-two', 'tag-four')) > 0;

Each condition in the having clause counts the number of rows (for a product) that match a condition. The first says there is at least one row with 'tag-one' handle. The second says that there is at least one row with the other two handles.

Upvotes: 2

Related Questions