Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Ok, after a lot of headaches and hours spent trying to get this working I found where it fails. First the (4) query is fine just need to do some adjustments. Now the query is this one: 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 –  ReynierPM May 17 '12 at 14:38
    
And I get the right messages just I didn't realize some mistakes in my way to see the things, my bad :( but now it's working so the question will be here for others that can have the same problem as me. –  ReynierPM May 17 '12 at 14:38
    
You can answer your own question (and even accept it). –  dezso May 17 '12 at 17:28
    
@dezso can't because my reputation doesn't allow me that yet –  ReynierPM May 17 '12 at 18:46
1  
AFAIK there is no reputation limit on answering any question. –  dezso May 18 '12 at 6:33
add comment

1 Answer

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;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.