mysql 排除另一表,MySQL SELECT的结果来自1个表，但排除结果取决于另一个表？

What SQL query would I have to use if I want to get the results from a table 'messages' but exclude rows that have the value in 'messages_view' where field messages.message=messages_view.id AND messages.deleted=1 AND messages_view.user=$somephpvariable

In more laymen terms, I have a messages table with each message denoted by an 'id' as well as a messages_view table connected with a 'message' field. I want to get the rows in message that are not deleted (comes from messages_view) for a specific 'user'. 'deleted'=1 when the message is deleted.

Here is my current SQL Query that just gets the values out of :

SELECT * FROM messages WHERE ((m_to=$user_id)

OR (m_to=0 AND (m_to_state='' OR m_to_state='$state')

AND (m_to_city='' OR m_to_city='$city')))

Here is the layout of my tables:

table: messages

----------------------------

id (INT) (auto increment)

m_from (INT)

m_to (INT)

m_to_state (VARCHAR)

m_to_city (VARCHAR)

table: messages_view

----------------------------

message (INT)

user (INT)

deleted (INT)

解决方案

I really think it's as simple as this:

SELECT * FROM messages WHERE ((m_to=$user_id)

OR (m_to=0 AND (m_to_state='' OR m_to_state='$state')

AND (m_to_city='' OR m_to_city='$city')))

AND NOT EXISTS (

SELECT *

FROM messages_view

WHERE messages.message = messages_view.id

AND messages.deleted = 1

AND messages_view.user = $somephpvariable

)