Multiple tag search in an associative table scheme

Question

I'm working with your standard three table tag setup

item_table
 item_id
 item_name

tag_table
 tag_id
 tag_name

tag_map
 map_id
 tag_id
 item_id

How this works should be obvious to anyone who has used a similar scheme for their tagging architecture, now, using this scheme I have a scenario in which I need to print the results of the item_table that match multiple tags, meaning they must have each tag presented to match and print the item from the item table, something written theoretically like this

SELECT i.item_id, i.item_name, t.tag_id, t.tag_name, m.tag_id, m.item_id FROM item_table AS i
JOIN tag_table AS t ON t.tag_id = m.tag_id
JOIN tag_map AS m ON m.item_id = i.item_id
WHERE t.tag_name = 'tag_one'
AND t.tag_name = 'tag_two'

And the desired output would be something like

item 1 (because the records for item 1 have assigned both tag_one and tag_two in the tag map) item 2 (ditto) (but not item 3 because it only has tag_one, or none of the tags)

Of course I know this wouldn't work, and why, I merely provided the mis-formed example to highlight what I'm trying to accomplish.

Thanks in advance for any advice.

Answer 1

try

SELECT i.item_id, i.item_name, t.tag_id, t.tag_name, m.tag_id, m.item_id 
FROM item_table AS i
JOIN tag_map AS m ON m.item_id = i.item_id
JOIN tag_table AS t ON t.tag_id = m.tag_id AND t.tag_name IN('tag1','tag2')
WHERE m.map_id NOT IN (SELECT m1.map_id FROM tag_map m1,item_table i1, tag_table t1
WHERE m1.item_id = i1.item_id AND m1.tag_id = t1.tag_id AND t1.tag_name IN('tag1','tag2')
GROUP BY m1.item_id HAVING COUNT(m1.item_id) <2)