MySQL - select all items with multiple hashtags

Question

I'm making hashtag system for my webpage, and have three tables:

• items - ID and some others
• tags - ID and name
• item_tags - junction table with IDitem and IDtag

Selecting all items with given hashtag is pretty easy:

SELECT items.* FROM items
join item_tags on items.ID=item_tags.IDitem
join tags on item_tags.IDtag=tags.ID
where tags.name="something";

Question is, what should I do if I want to select all items with multiple tags, for example, find all items tagged as cat and animal?

I've thought about making temporary table, inserting all items with first tag, then leaving those with second tag, then third, then fourth and so on, but it doesn't look too good and too fast.

Answer 1

Just find all that match both tags, using IN. Like this:

SELECT DISTINCT items.* FROM items 
INNER JOIN item_tags on items.ID=item_tags.IDitem 
INNER JOIN tags on item_tags.IDtag=tags.ID 
WHERE tags.name="something"
AND items.* IN (
    SELECT items.* FROM items 
    INNER JOIN item_tags on items.ID=item_tags.IDitem 
    INNER JOIN tags on item_tags.IDtag=tags.ID 
    WHERE tags.name="somethingelse"
);

Answer 2

well you know your list, so that is a simple string. and you know your count. these can be jammed into a mysql Prepared Statement and executed.

But below it is with the list and count plopped in just for demo purposes.

create table items
(   id int not null
);

create table tags
(   id int not null,
    name varchar(50)
);

create table item_tags
(   iid int not null,
    tid int not null
);

insert items (id) values (1),(2),(3),(4);
insert tags(id,name) values (1,'cat'),(2,'animal'),(3,'has nose');
-- note, everything has a nose so far:
insert item_tags (iid,tid) values (1,1),(1,3),(2,1),(2,3),(3,2),(3,3),(4,1),(4,2),(4,3);

select i.id,count(i.id)
from items i
join item_tags junc
on junc.iid=i.id
join tags t
on t.id=junc.tid and t.name in ('cat','animal')
group by i.id
having count(i.id)=2

-- only item 4 has both cat and animal (note nose is irrelevant)