SQL question: find items tagged with specific tags

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'

Any help is very much appreciated!
Ingo


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: SQL question: find items tagged with specific tags

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/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: SQL question: find items tagged with specific tags

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/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: SQL question: find items tagged with specific tags

Ingo Weiss schrieb:
> 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':

oh ... "and" ..., i missred

SELECT DISTINCT items.*
FROM items
INNER JOIN taggings
ON items.id = taggings.item_id
INNER JOIN tags
ON tags.id = taggings.tag_id
AND tags.name = 'blue'
AND tags.name = 'red';

or

SELECT DISTINCT items.*
COUNT(items.id)
FROM [your join above]
WHERE tags.name IN ('blue', 'red')
HAVING COUNT(items.id) = 2;

--
Sebastian Mendel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org