1

Hopefully this explanation will clear up what the question is asking for ...

I have 2 tables (I'll populate them a little too):

inbox

---------------|-----------------
| to_USER_id   |   MESSAGE_id   |
---------------|-----------------
|       2      |         1      |
|       3      |         2      |
|       4      |         3      |
---------------------------------

message

---------------|----------------------------------
|     id       |  from_USER_id   |    content    |
--------------------------------------------------
|      1       |        4        |      Hey      |
|      2       |        1        |     Hello     |
|      3       |        2        |   How's it..  |
--------------------------------------------------

Basically, these are the tables that hold data when messages are 'sent' from one user to another. I need to work out a query (MySQL) that will return the message.id, which does not have an inbox.to_USER_id the same as the message.from_USER_id (Basically, which message has not been replied to)

So, as you may be able to see, user 4 messages user 2. Then user 1 messages user 3. Then user 2 replies to user 4.

How do I query these tables to only return the message.id which has not yet had a reply. So, in this case it will return the row where a message was sent from user 1, which has not got a reply from user 3.

It's quite hard to explain ... But hopefully someone understands

2 Answers 2

1
SELECT  m.id
    FROM  message AS m
    WHERE  NOT EXISTS 
      ( SELECT  *
            FROM  inbox AS i
            WHERE  i.message_id = m.id
              AND  i.to_user_id = m.from_user_id 
      ) 

I think it could also be cone with a LEFT JOIN (with similar efficiency), but NOT EXISTS more closely matches your description of the task.

3
  • Thanks, your answer got my brain working a little, and managed to get it working using NOT IN rather than NOT EXISTS. I wont post the full results, because I still don't think my question actually makes sense Lol ... I'll probably delete this question tomorrow, just wanted to say thanks for your time :D Mar 7, 2015 at 23:56
  • Keep in mind that [NOT] IN ( SELECT ... ) is inefficient. You explained it well enough for me to figure out what you wanted.
    – Rick James
    Mar 8, 2015 at 0:06
  • I'll put what I did as an answer, so that you can see ... Again, I think it's because I have simplified my tables, and my explanation is not clear Lol ... Mar 8, 2015 at 0:16
0

This answer is just to explain a comment

SELECT id
FROM message
WHERE from_USER_id NOT IN
    (SELECT to_USER_id
    FROM inbox 
    LEFT JOIN message ON message.id=inbox.MESSAGE_id)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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