dssk35460
dssk35460
2016-07-21 20:24

mySql查询中间表

已采纳

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.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • dsubq24666 dsubq24666 5年前

    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.

    点赞 评论 复制链接分享
  • dougu2240 dougu2240 5年前

    Try this:

    select c1.user_id as user1, c2.user_id as user2 
    from conversation_user c1, conversation_user c2 
    where c1.conversation_id = c2.conversation_id and not c1.user_id = c2.user_id
    
    点赞 评论 复制链接分享