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 have two table as one is the message table and another one is messageUser table. Now i need to check before insert a new row.

for example,

M_Message
MessageId
=========
               1
               2
               3

M_MessageUser
MessageId | MemberId | BusinessId
=========|=========|=========
               1 |               1 |               0
               1 |               0 |               2
               2 |               1 |               0
               2 |               0 |               2
               2 |               3 |               0
               2 |               4 |               0
               3 |               1 |               0
               3 |               0 |               2
               3 |               0 |               4

if a member create a new message, i would like to check the user in this message is there exist before. If yes, then attach the message to previous conversation else create a new conversation.

Scene 1
Member 1 sent a message to Business 2, from the table we know that there have a previous conversation which is Message 1

Scene 2
Member 1 sent a message to Business 2 & Member 3, from the table we know that there is no previous conversation

I've tried before using UNION, IN for the checking query but basically is just get back all the list. Is there anyone can give me a help? Thanks.

UPDATE

I can solve scene 1 by using but failed to suit scene 2

    SELECT MessageId FROM M_MessageUser
    WHERE (MemberId IN (0,1) AND BusinessId IN(0,2))
    GROUP BY MessageId
    EXCEPT
    SELECT MessageId FROM M_MessageUser
    WHERE (MemberId NOT IN (0,1) OR BusinessId NOT IN(0,2))
    GROUP BY MessageId;
share|improve this question
    
Can you explain what the data in the messageUser table means? The examples you gave make absolutely no sense to me with regards to the sample data. –  Jon Seigel Mar 26 '13 at 2:51
    
data in messageUser table is typically represent the user in the message / conversation / group chat, it is because there will be more than 2 person in a message... Live example is Facebook chat, you can start a conversation with a user or a group of users... the next time you create a new conversation between him/her/them, Facebook will automatically append it to existing conversation due there are same group of person in the conversation... –  Kelvin Go Mar 27 '13 at 4:13

1 Answer 1

up vote 0 down vote accepted

I would suggest to handle your scenario using the application rather than using the DB. For example, if a member creates a message:

  1. Check if the user in the message exists. Query the DB for this.
  2. Based on the result from the query above: if the user exists, update the appropriate row (DB update).
  3. If the user does not exist, insert the row (DB insert).

Note that in the above scenario, the application is making the logical decisions. If you want the DB to do this logical processing, then you can use an instead of insert trigger.

share|improve this answer

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.