SQL: Select Rows which does not have a corresponding userID in second table

Question

I have 2 tables below :

Messages

On this table, for a particular Message, if it has a type 'Q' it's a question. Else it's a conversation from 2 users.

Conversation Table

This keeps a track of conversations and has questionuserid and answer userid

Now from the table above, I would like to get all the messages which are not of the type Q and the userid of that message should not be the answeruserid for the corresponding question in conversation table. The id in messages table is the questionid in conversation table.

This is what I tried :

SELECT
    *
FROM
    chekmarc.messages AS a
WHERE
    userid NOT IN
    (
        SELECT
            answeruserid
        FROM
            chekmarc.conversations AS b 
       WHERE
            a.id = b.questionid 
    )
    AND
    a.type != 'Q';

But NOT IN corresponds to multi-level referencing, so won't work here.

This is what I tried next :

SELECT
    m.*,
    c.*
FROM
    chekmarc.messages m
    INNER JOIN
    (
        SELECT
            *
        FROM
            chekmarc.conversations c 
        WHERE
            answeruserid IS NOT NULL
            AND
            questionid IS NOT NULL
    ) c ON
        c.questionid = m.id
        AND
        m.userid = c.answeruserid
WHERE
    m.type != 'Q'
    AND
    m.userid IS NOT NULL
    AND
    m.id IS NOT NULL;

But I get not matching rows and desired result. How can I achieve this?

Thanks.

UPDATE :

• One id from messages table can be multiple questionid from conversations table. When the user starts the conversation, a new row is inserted under conversations and refer the questionid ( messages id with type = 'Q' that was posted)
• For example, when I (say user A) post a question say 'help me', multiple users now can start a conversation with respect to this question. Now 'help me' is stored in messages with a type='Q' to show it was a question. When a user say user B starts a conversation with me on question 'help me', a new row is inserted under conversations showing user A userid (as questionuserid) and user B ( as answeruserid). From now on any messages will be stored under messages table (either user A and B). So if we were to get all the convos related to user B for a message, we can check under conversation if userid in messages table is not the questionuserid in conversation table
• The tables have no relation constraints

Answer 1

I would like to get all the messages which are not of the type Q and the userid of that message should not be the answeruserid for the corresponding question in conversation table. The id in messages table is the questionid in conversation table.

I think you want not exists:

SELECT m.*
FROM  messages 
WHERE m.type <> 'Q' AND
      NOT EXISTS (SELECT 1
                  FROM conversations c
                  WHERE m.id = c.questionid AND
                        m.userid = c.answeruserid
                 );

Answer 2

I believe this will work:

SELECT
    *
FROM
    messages AS m1
    INNER JOIN conversations AS c1 ON m1.conversationid = c1.id
WHERE
    m1.type <> 'Q'
    AND
    m1.userid <> c1.answeruserid

Remember that a JOIN (matching rows) is not the same thing as a WHERE x IN ( SELECT y FROM z ) predicate, even though they can both cause the same results (but only in trivial queries).

Understanding how JOINs work (and when to use them) is one of the hardest parts to grok about SQL. I wish SQL tutorials and guides would introduce subqueries after JOINs, otherwise people get the wrong mental model of SQL.