3 ms·
SELECT * FROM post WHERE id IN ( select post_id from post_tags INNER JOIN tags ON post_tag.tag_id = tag.id WHERE tag.tag in ("foo","bar","ba
by SigmundA 14y ago
SELECT * FROM post
WHERE id IN
(
select post_id from post_tags
INNER JOIN tags ON post_tag.tag_id = tag.id
WHERE tag.tag in ("foo","bar","baz")
GROUP BY post_id
HAVING COUNT(*) = 3
)
AND author IN ("Joe","Jane")
ORDER BY post_date DESC;
Of course normalizing tags is silly as they are natural keys so it should be as you first stated:
SELECT * FROM post
WHERE id IN
(
select post_id from post_tags
WHERE tag in ("foo","bar","baz")
GROUP BY post_id
HAVING COUNT(*) = 3
)
AND author IN ("Joe","Jane")
ORDER BY post_date DESC;
- cheald 14y agoAre subselects still ridiculously inefficient in MySQL? It's been a long while since I've used them. I do like how those queries read, though.
- SigmundA 14y agoSorry I only use proper DB's like PostgreSQL and MSSQL who optimizer's have no problems with this ;). Also what I like about the count approach is you can do "show me all the posts with at least 2 out of the 3 tags matching" fuzzier search if you desire.