[RESOLVED] MySQL select when two criteria are met

jme

Hi all,

I have a site where posts are tagged. The tags are stored in a table with the this structure:

id         postid      tag
1          1            this
2          1            that
3          2            this
4          2            foo
5          3            bar

I have created a search by tag script (in php) which gives all posts tagged with a specific tag using a simple (SELECT postid FROM tags where tag = '{$_GET['tag']}'). I am now attempting to allow users to search for a post which has two specific tags - I have already worked out how to create another mini-tag cloud (see image) of all those tags which the selected posts (from the first tag query) have. I am struggling with the SELECT query to get a list of posts which have those two specific tags for the two-tags-search.php page.

Basically, I need a query which (using the above table as an example) would return a postid of 1 if I passed 'this' and 'that' to the query. I have tried using sub-queries with no success.

Any advice would be appreciated.

Thanks in advance.

jme

I've just cracked it. In case anyone else needs the code:

SELECT COUNT(*), t1.* FROM  (

((SELECT * FROM tags WHERE tag = 'TAG1') UNION 
(SELECT * from tags WHERE tag = 'TAG2')) as t1 

) 
GROUP BY t1.postid HAVING COUNT(*) > 1

bradgrafelman

jme;10956448 wrote:
I've just cracked it. In case anyone else needs the code:
SELECT COUNT(*), t1.* FROM  (

((SELECT * FROM tags WHERE tag = 'TAG1') UNION 
(SELECT * from tags WHERE tag = 'TAG2')) as t1 

) 
GROUP BY t1.postid HAVING COUNT(*) > 1

Ouch! Seems like it would be a lot more efficient to simply JOIN the table onto itself, e.g.:

SELECT postid
FROM post p1
    INNER JOIN post p2 USING(postid)
WHERE p1.tag = 'this'
    AND p2.tag = 'that'