Thanks, Sebastian!
I have tried this one before. The problem is that it finds all items
the tags of which include EITHER 'blue' OR 'red', not 'blue' AND 'red':
mysql> SELECT DISTINCT items.title from items inner join taggings on
(items.id = taggings.item_id) inner join tags on (tags.id =
taggings.tag_id) WHERE tags.name IN ('red', 'blue');
+-------------------------------+
| title |
+-------------------------------+
| tagged_red |
| tagged_red_and_blue |
| tagged_red_and_green |
+-------------------------------+
Do you have an idea how to create an AND query?
Ingo
On Apr 22, 2008, at 5:42 PM, Sebastian Mendel wrote:
Ingo Weiss schrieb:
Hi all,
I have an application where items can be tagged. There are three
tables
'items', 'taggings' and 'tags' joined together like this:
items inner join taggings on (items.id = taggings.item_id) inner join
tags on (tags.id = taggings.tag_id)
Now I have been struggling for some time now with coming up with
the SQL
to find the items the tags of which include a specified list of tag
names. Example:
I am looking for items tagged with 'blue' and 'red'. This should
find me:
- items tagged with 'blue' and 'red'
- items tagged with 'blue', 'red' and 'green'
SELECT DISTINCT items.*
FROM [your join above]
WHERE tags.name IN ('blue', 'red');
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
