MySQL SELECT results from 1 table, but exclude results depending on another table?
- by Brandon
Hey,
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) <-- Represents a user id (0 = site admin)
m_to (INT) <-- Represents a user id (0 = all users)
m_to_state (VARCHAR)
m_to_city (VARCHAR)
table: messages_view
----------------------------
message (INT) <-- Corresponds to messages.id above
user (INT) <-- Represents a user id
deleted (INT) <-- 1 = deleted
