排除另一个表中存在的记录

I got 2 tables like these:

emails:
emailID int(10) auto_increment, memberID int(10), emailData text, and so on

members:
memberID int(10) auto_increment, user_name char(40), password char(50), and so on

My query is this:

select 
emails.emailID, emails.emailData, 
members.memberID, members.user_name 
from emails, members where
emails.memberID = members.memberID

Now I've added two more tables like these:

blocked:
id int(10) auto_increment, memberID int(10), blocked_memberID int(10)

markedAsRead:
id int(10) auto_increment, memberID int(10), emailID int(10)

I want to modify my original query so that it excludes memberID which are in blocked.blocked_memberID and also excludes emailID which are in markedAsRead.emailID

How can I do this?

You can use NOT EXISTS :

SELECT ....
FROM   .... 
WHERE  ..... // Replace the dots with Your Query
  AND NOT EXISTS(SELECT 1 FROM blocked
                 WHERE emails.memberID = blocked.memberID)
  AND NOT EXISTS(SELECT 1 FROM markedAsRead
                 WHERE emails.emailID = markedAsRead.emailID)

You could also lookup for LEFT JOINS or NOT IN to exclude records that doesn't exists in a particular table.

EDIT: Usually EXISTS() and LEFTJOIN have similar performaces, sometime it can even perform better than a join.

LEFT JOIN sulotion:

SELECT ...
FROM ...
LEFT JOIN blocked 
 ON(WHERE emails.memberID = blocked.memberID)
LEFT JOIN markedAsRead
 ON(emails.emailID = markedAsRead.emailID)
WHERE ...
 AND blocked.memberID IS NULL
 AND markedAsRead.emailID IS NULL