mySql查询中间表

Question

i try to find out if a conversation between two users exist but i can't imagine how to query this in mysql.

My tables are:

users
-----
id
name

conversations
-------------
id

conversation_user
-----------------
user_id
conversation_id

For example, i have two users and one conversation so in my conversation_user table i have

user_id    conversation_id
1          1
2          1

How to check if this relationship exist or not?

thank you in advance.

Answer 1

You can use join for that:

SELECT
    c1.*, c2.*
FROM
    conversation_user c1
INNER JOIN
    conversation_user c2
ON
    c1.conversation_id = c2.conversation_id
WHERE
    c1.user_id != c2.user_id;

---

You can take a look at this SQLFiddle Example that is a bit more detailed. It shows an example of bringing the name of the users and one that filters by conversation.