Q:

Mysql select rows using cross reference but omit on a condition

Given these tables:
+---------------+---------+
| Field         | Type    |
+---------------+---------+
| group_id      | int(10) |
| subscriber_id | int(10) |
+---------------+---------+

+---------------+--------------+
| Field         | Type         |
+---------------+--------------+
| subscriber_id | int(10)      |
| firstname     | varchar(50)  |
| lastname      | varchar(50)  |
| company       | varchar(120) |
| position      | varchar(50)  |
| email         | text         |
| lettertype    | varchar(5)   |
| status        | varchar(20)  |
+---------------+--------------+

I used the following query to get a subset of subscribers:
SELECT * 
FROM newsletter_subscribe AS a, newsletter_subscriber AS b
WHERE (a.group_id = 1 or a.group_id = 4)AND (a.subscriber_id = b.subscriber_id)

What I'd like to do is exclude from the subset if a row exists in newsletter_subscribe where group_id = 3 then the newsletter_subscribe from that row is excluded from the result.
My thought was to make a temporary table to replace a, but I'm not certain how to go about it.

A:

SELECT * 
FROM newsletter_subscribe AS a, newsletter_subscriber AS b
WHERE (a.group_id = 1 or a.group_id = 4) AND (a.subscriber_id = b.subscriber_id) AND (b.subscriber_id NOT IN (SELECT subscriber_id FROM newsletter_subscribe WHERE group_id = 3))

