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

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.

| improve this answer | |
  • 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 – Just Lucky Really Mar 7 '15 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 '15 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 ... – Just Lucky Really Mar 8 '15 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)
| improve this answer | |

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.