[SOLVED] Help Selecting on multiple conditions

[TheGonk]

Okay, so I am stumped on something which seems easy enough logically, but I can't figure out how to write it as an SQL statement.  I have articles and tags for those articles.  I want to find any articles which all have a particular set of tags.

 

Here's the DB:

mysql> describe tag;
+---------+-----------------------+------+-----+---------+-------+
| Field   | Type                  | Null | Key | Default | Extra |
+---------+-----------------------+------+-----+---------+-------+
| t_id    | smallint(5) unsigned  |      |     | 0       |       |
| u_id    | varchar(32)           |      |     |         |       |
| item_id | mediumint( unsigned |      |     | 0       |       |
+---------+-----------------------+------+-----+---------+-------+

 

Okay, if I'm just looking for a single tag, it's essentially trivial:

 

mysql> select item_id from tag WHERE t_id=12 ;

 

I figured out a way to do exactly two tags by joining the table with itself, but obviously that's not going to work beyond two tags.

 

mysql> select distinct p1.item_id from tag AS p1 INNER JOIN tag AS p2 ON p1.item_id=p2.item_id WHERE p1.t_id=12 AND p2.t_id=42;
+---------+
| item_id |
+---------+
|      49 |
|     147 |
+---------+
2 rows in set (0.00 sec)

 

So...how do I write this in the general case?  And how do I expand that to exclude tags?

 

Thanks for the help!

[TheGonk]

I suppose I could fill a temporary table with the results of the first two, and then iterate through the rest, building a new, narrower table each time?  That just sounds like it's more complicated than it should be.

[artacus]

I've got an idea that will work, but I don't think its very elegant. Maybe someone will post a better solution, but until then...

 

SELECT items.*
FROM items
JOIN (
    SELECT item_id, GROUP_CONCAT(t_id) AS tids
    FROM tag 
    GROUP BY item_id
) AS sub ON items.id = sub.item_id
WHERE FIND_IN_SET(12, sub.tids) AND FIND_IN_SET(42, sub.tids)

[TheGonk]

That worked great, thanks!  It uses a few things that I'm not familiar with, but look really useful, like GROUP_CONCAT and FIND_IN_SET.  I can NOT the FIND_IN_SETs and get exactly what I want.

 

SELECT tag.* 
FROM tag
JOIN (
     SELECT item_id, GROUP_CONCAT(t_id) AS tids
     FROM tag
      GROUP BY item_id 
) AS sub ON tag.item_id = sub.item_id
WHERE FIND_IN_SET(12, sub.tids) AND FIND_IN_SET(42, sub.tids) AND NOT FIND_IN_SET(43, sub.tids)
+------+----------------------------------+---------+
| t_id | u_id                             | item_id |
+------+----------------------------------+---------+
|   42 | 75a609f7435237631d460018b7a855bc |     147 |
|   12 | 75a609f7435237631d460018b7a855bc |     147 |
+------+----------------------------------+---------+