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?
解决方案
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
OUTPUT
╔══════╦════════════╦═══════╗
║ MD5 ║ URL ║ TITLE ║
╠══════╬════════════╬═══════╣
║ a0a0 ║ google.com ║ foo ║
╚══════╩════════════╩═══════╝