How to get non-deleted messages for one user_id if the same user_id appears in two tables at same time?

Question

I'm trying to get non-deleted (deleted = 0) messages for one user_id if the same user_id appears in two tables at same time, but something is wrong because I am not getting the correct or expected data. This post is related to this other question (from me too).

So basically I have 3 tables:

• default_recipient: stores user_id and message_id for not to repeat messages at default_messages table so when I reply to a message or send multiple users messages user_id and message_id references are stored at this table

• default_messages: stores messages fields among sender user_id

• default_profiles: stores fields related to users

Since the message is just one for sender and for receiver, then when one of the sides wants to delete and the other does not, then messages can't be physically deleted. So I mark the message with a flag deleted = 1.

If the same message_id has deleted = 1 in default_messages and also in default_recipient, then the message will be deleted physically because both parts sender and receiver deleted the message from their inbox.

Now I have these queries just for testing and see results:

1) select sender_user_id, deleted from default_messages;
2) select * from default_recipient;
3) SELECT DISTINCT p.*, rcp.*, msg.*, msg.id as message_id FROM default_messages msg          
        LEFT JOIN default_recipient rcp ON (msg.id = rcp.message_id)         
        LEFT JOIN default_profiles p ON (p.user_id = msg.sender_user_id)         
        WHERE msg.sender_user_id = 1 OR rcp.user_id = 1         
        AND msg.deleted = 0       
        GROUP BY (p.id)  
        ORDER BY msg.date DESC;
4) SELECT rcp.user_id as rcpuid, rcp.message_id as rcpmid, msg.sender_user_id, msg.id as message_id FROM default_messages msg 
        LEFT JOIN default_recipient rcp ON (msg.id = rcp.message_id)
        LEFT JOIN default_profiles p ON (p.user_id = msg.sender_user_id)
        WHERE (msg.sender_user_id = 1 OR rcp.user_id = 1)
           AND (msg.deleted = 0 OR rcp.deleted = 0)
        ORDER BY msg.date DESC;

What I'm trying to do is get all messages from a specific user where the message aren't deleted. So taking as example user_id = 1, then I need to get those messages where default_messages.sender_user_id = 1 OR default_recipient.user_id = 1 AND default_messages = 0 AND default_recipient.deleted = 0 meaning: messages for user = 1 where deleted = 0.

Now with my data as you can see here and with queries 3 and 4 the results aren't correct and I'm looking to fix this query.

Any help is appreciated

Answer 1

The problem is query #4 needs a small adjustment. Now the query is:

SELECT p.*
    , rcp.*
    , msg.*
    , msg.id as message_id 
FROM default_messages msg 
    LEFT JOIN default_recipient rcp ON (msg.id = rcp.message_id) 
    LEFT JOIN default_profiles p ON (p.user_id = msg.sender_user_id) 
WHERE (msg.sender_user_id = 1 AND msg.deleted = 0) 
       OR (rcp.user_id = 1 AND rcp.deleted = 0) 
ORDER BY msg.date DESC;