2

I don't really know how to explain except with "vertical where".

Imagine the following table:

TAGID|PRODUCTID|SHOP_ID
59   |3418-7   |38
61   |3418-7   |38
60   |4227-4   |38
61   |4227-4   |38

Now I want to return all product IDs, that have relation to the tag IDs: 59,61. In other words, values of product ID where rows exist for both tag IDs.

So I want to return 3418-7, but not 4227-4

How do I write this as simple as possible in a SQL statement?

This is the working statement I have so far, but I feel this could be done in a much smarter way:

SELECT 
  productid 
FROM shop_tag_relations 
WHERE 
  productid IN (select productid from shop_tag_relations WHERE tagid=59) 
AND 
  productid IN (select productid from shop_tag_relations WHERE tagid=61)
GROUP BY productid,shop_id
paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
Esben
  • 1,943
  • 1
  • 18
  • 37
  • What does this mean? "all product id's, that have relation to the tagids: 59,61" – YXD Feb 14 '12 at 12:41
  • Return the productid which are related to tagid 59 and 61. This is a relations table which draws relations between products in one table and tags in another. If for instance 3418 is a brown shoe for men, tag 59 might be shoe, tag 61 might be men, 72 brown etc. – Esben Feb 14 '12 at 12:45

3 Answers3

8
SELECT PRODUCTID
FROM T
WHERE TAGID IN (59,61)
GROUP BY PRODUCTID
HAVING COUNT(DISTINCT TAGID) = 2

Or

SELECT PRODUCTID
FROM T
WHERE TAGID = 59
INTERSECT
SELECT PRODUCTID
FROM T
WHERE TAGID = 61
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • @EugenRieck - With the right indexes `JOIN` or `INTERSECT` should be just as fast or faster than `GROUP BY`. They are just less convenient to extend. – Martin Smith Feb 14 '12 at 12:49
  • 1
    INTERSECT is much less portable than GROUP BY, JOIN is much more complicated than GROUP BY with more tags - so in the end of the day GROUP BY is what I suspect to be the optimal solution – Eugen Rieck Feb 14 '12 at 12:52
2
SELECT DISTINCT 
a.PRODUCTID 
FROM mytable AS a
INNER JOIN mytable AS b ON a.PRODUCTID=b.PRODUCTID
WHERE a.TAGID=59
AND b.TAGID=61
;
Eugen Rieck
  • 64,175
  • 10
  • 70
  • 92
  • 1
    Every solution to this problem has either a scaling problem (Your initial solution, my answer) or is not portable (Intersect). Chose your poison. Point is, that the JOIN solution will be highly optimized by the query planner, is there is an index on TAGID. – Eugen Rieck Feb 14 '12 at 12:47
1
SELECT ProductId
FROM shop_tag_relations 
WHERE TAGID IN (59,61)
GROUP BY ProductId
HAVING COUNT(DISTINCT TagId) = 2
Lamak
  • 69,480
  • 12
  • 108
  • 116