How to efficiently code a query in mysql to AND tags?

Question

I have a common problem to be sure. I'd like to make a query that finds an entity that has "n" tags. So in the simplest case, we find all the entities that have the tag "hey". In a more complex case, we find all the entities that have all the tags "hey", "hi" and "howdy".

It seems that I have to join to the tag table 3 times, and and thus create 3 different aliases. In the abstract case, I will have to make N different aliases. Is there a simpler way to achieve this?

The reason I am asking is that I need to write a query that not only does this for tags, but for a variety of things. So I am basically going to join NxM aliases... which is going to suck to write (and tune) the query.

Help?

EDIT:

Nevermind. I found the solution:

select distinct g.id, g.description
FROM gallery g
 inner join gallery_to_tag g2t_0
  on g2t_0.gallery_id = g.id
 inner join tag t_0
  on t_0.id = g2t_0.tag_id and t_0.term = 'hi'

 inner join gallery_to_tag g2t_1
  on g2t_1.gallery_id = g.id
 inner join tag t_1
  on t_1.id = g2t_1.tag_id and t_1.term = 'hey'

Answer 1

Is this what you might be looking for. Its hard to tell without out seeing your schema

CREATE TEMPORARY TABLE `checktags` (
  `checkme` varchar(100) NOT NULL,
  KEY `checkme` (`checkme`)
) ENGINE=MyISAM;

//INSERT ALL TAGS TO CHECK

SELECT * FROM `table` 
LEFT JOIN `tags` ON `table_id` = `tag_table_id` 
WHERE `tag` IN (SELECT `checkme` FROM `checktags`);

Answer 2

It is difficult to provide a precise answer without the schema, however, I believe you are asking how to determine that a given item has all the tags being queried. You could do something like

Select ..
From MainTable
Where Exists    (
                Select 1
                From TagsTable
                Where TagsTable.FK = MainTable.PK
                    And TagsTable.Tag In('hey','hi','howdy')
                Having Count(*) = 3
                )

Answer 3

If you want to avoid joining tables, you will need some subqueries. So for N tags, you would need N subqueries.

SELECT * FROM Entities
WHERE id IN (SELECT entity_id FROM tags WHERE tag = 'Hey')
AND id IN (SELECT entity_id FROM tags WHERE tag = 'Hello')
AND ...

And the same way, you can select entities that have any of some desired tags:

SELECT * FROM Entities
WHERE id IN (SELECT entity_id FROM tags WHERE tag = 'Hey')
OR id IN (SELECT entity_id FROM tags WHERE tag = 'Hello')
OR ...