mysql join-选择col1，其中col2匹配其他表中的(已过滤)col [英] mysql join - select col1 where col2 matches a (filtered) col in other table

问题描述

这就是我想要做的:

1. select tableB.column1 WHERE tableB.column2 = some_var

select tableA.column1 WHERE tableA.column2 was a result from step one.

可以一键完成吗?做到这一点的最佳方法是什么，特别是在性能方面?我正在使用PHP.

Can this be done in one select? What's the best way to do this, especially performance-wise? I'm using PHP.

我有一个基于对话的消息传递系统. 1 convo table和1 replies table.答复表包括列:responder and convo_id.要加载convo，我会收到所有带有convo_id的回复.

I have a conversation based messaging system. 1 convo table and 1 replies table. Replies table includes columns: responder and convo_id. To load convo I get all replies with that convo_id.

我想使用convoId来获取该convo的所有响应者，并进入用户表并选择所有响应者的化身，然后将这些化身存储在数组中，并根据需要将它们回显.

I want to use the convoId to get all responders to that convo, and go into usertable and select all the avatars of responders, then store the avatars in array and echo them out as needed.

推荐答案

您可能想尝试以下sql语句

You may want to try the following sql statement

SELECT u.avatar
FROM usertable u
JOIN replies r ON u.responder_id = r.responder_id
WHERE r.convo_id = 1234

这将获得ID为1234的参与对话的用户的所有化身

this will get all the avatars of the users who participated in the conversation with id 1234

这篇关于mysql join-选择col1，其中col2匹配其他表中的(已过滤)col的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！