1

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;
2
  • 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
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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