MySQL - Relational Databases and Tagging

Question

I have been scratching my head on this one for a while, so I figured I'd ask stack overflow (Note: I am an SQL novice trying to learn more SQL, so please be respectful and explanatory):

I have one sql table that looks like this called "posts":

id | user
--------------------------------
0  | tim
1  | tim
2  | bob

And another called "tags" that stores the tags on the posts (in the "posts" table) in text:

id | postID | tag
--------------------------------
0  | 0      | php
1  | 2      | php
2  | 0      | mysql
3  | 1      | mysql
4  | 1      | sql
5  | 3      | perl

(To clarify, the concept where: id=0 is tagged php,mysql; id=1 is tagged sql,mysql; id=2 is tagged php; id=3 is tagged perl.)

How could I write a WHERE statement to get posts tagged x, but not y (x and y will be defined by php)?

For example, how could I get all posts tagged mysql but not php?

EDIT

Could you also explain how to add multiple tags to search for (for example get all tagged mysql and recursion but not php)

Answer 1

select *
from
    (select distinct postID
    from tags
    where tag = "mysql") as t1
left join
    (select distinct postID
     from tags
     where tag = "php") as t2
using (postID)
where t2.postID is NULL

example 2: get all tagged mysql and recursion but not php:

select *
from
    ((select distinct postID
    from tags
    where tag = "mysql") as t1
join
    (select distinct postID
    from tags
    where tag = "recursion") as t3
using (postID))
left join
    (select distinct postID
     from tags
     where tag = "php") as t2
using (postID)
where t2.postID is NULL