我在尝试进行简单查询时遇到麻烦。我的表是:
用户
+-------+----------+
| id | username |
+-------+----------+
讯息
+-------+-------------+------------+--------+
| id | receiver_id | sender_id | text |
+-------+-------------+------------+--------+
我需要获取来自SEPECIFIC用户的所有消息或从SEPECIFIC用户接收(知道其用户名)。但是我还需要获取接收者和发送者的用户名。
我用了
SELECT * from user U, messages M where (M.sender_id OR M.receiver_id)=(select id from user where username = 'Guy1') group by M.id
这可行,但是现在我需要他们的用户名,所以我想到了类似的东西:
SELECT * from user U, messages M, (select username as Sender from user U1, messages M1 where M1.sender_id= U1.id)as Sub where (M.sender_id OR M.receiver_id)=(select id from user where username = 'Guy1') group by M.id
但这并不能满足我的需求,我该如何实现自己的目标呢?
结果,我需要这样的东西:
+-------+-------------------+------------------+
| text | receiver_Username | sender_Username |
+-------+-------------------+------------------+
我认为以下查询通过将两次message表与user表联接在一起将获得您需要的结果。一个给发送者,一个给接收者
SELECT messages.text,sender.username,receiver.username from messages
inner join user as sender on sender.id = messages.sender_id
inner join user as receiver on receiver.id = messages.receiver_id
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句