This seems very basic but I can't figure it out.
I've got a table "item_tags", and I want to select all of the items that match tags 1 and 2 (as in, each item has to have both tags).
How would I do this in mysql?
Create table is:
CREATE TABLE `item_tags` (
`uid_local` int(11) NOT NULL DEFAULT '0',
`uid_foreign` int(11) NOT NULL DEFAULT '0',
`sorting` int(11) NOT NULL DEFAULT '0',
KEY `uid_local` (`uid_local`),
KEY `uid_foreign` (`uid_foreign`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Thanks!
Use:
SELECT i.uid
FROM ITEMS i
JOIN ITEM_TAGS it ON it.uid_local = i.uid
AND it.uid_foreign IN (1, 2)
GROUP BY i.uid
HAVING COUNT(DISTINCT it.uid_foreign) = 2
You need to have a GROUP BY and HAVING clause defined, and the count of distinct tag ids must equal the number of tags you specify in the IN clause.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With