Select row from one query where opposites are not present in second query

Question

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

Answer 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.

Answer 2

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)