mysql: finding rows that have multiple tags and the same id

join, mysql, select, sql

Solution

The type of problem is called `Relational Division`

SELECT  a.md5, 
        a.url,
        a.title
FROM    Links a
        INNER JOIN Tags b
            ON a.md5 = b.md5
WHERE   b.Tag IN ('awesome', 'useful') -- <<== list of desired tags
GROUP   BY a.md5, a.url, a.title
HAVING  COUNT(*) = 2                   -- <<== number of tags defined

- SQLFiddle Demo

- SQL of Relational Division

OUTPUT

╔══════╦════════════╦═══════╗
║ MD5  ║    URL     ║ TITLE ║
╠══════╬════════════╬═══════╣
║ a0a0 ║ google.com ║ foo   ║
╚══════╩════════════╩═══════╝

Problem

I'm having an issue figuring the mysql to find links that have two specific 'tags' and the same 'hashid' when doing a JOIN of two tables Assume my tables look like this: Links ``` md5 url title numberofsaves ----------------------------------------- a0a0 google.com foo 200 b1b1 yahoo.com yahoo 100 ``` Tags ``` md5 tag --------------- a0a0 awesome a0a0 useful a0a0 cool b1b1 useful b1b1 boring ``` I want to return rows that have tags of BOTH 'useful' and 'awesome' The current (working/ fast) query for finding links by 1 tag: ``` SELECT links.title, links.numsaves FROM links LEFT JOIN tags ON links.md5=tags.md5 WHERE tags.tag = 'useful' ORDER BY links.numberofsaves DESC LIMIT 20 ``` After reading an article I tried to use the following: ``` SELECT links.title, links.numsaves FROM links LEFT JOIN tags ON links.md5=tags.md5 GROUP BY tags.md5 HAVING SUM(tags.tag='useful') AND SUM(tags.tag='awesome') ORDER BY links.numberofsaves DESC LIMIT 20 ``` This does work but it is so unbelievably slow as to be unusable. Anyone know the solution?

Original source