从多个列获取不同的值（包括联接）

发布于 2024-12-13 13:49:05 字数 1264 浏览 2 评论 0原文

这个把我难住了。

快速概述

我需要能够获取参与消息线程的人员的所有电子邮件地址。人们可以通过几种不同的方式将消息添加到此消息线程，并且由于人们可以是消息的发件人或接收消息，因此电子邮件地址可能位于不同的列中。

这是一个示例表。

messagesTable

ID | threadKey |  fromEmail   |  toEmail    | sentBy   |   sentTo
1  | ASJHD2    | [email protected]  | [email protected] |  null    |   null
2  | ASJHD2    | [email protected]   |             |  null    |   null
3  | ASJHD2    |              |             |  55      |   88

我还有我的 Users 表。

   ID   | username  | email
   55   | Ike       | [email protected]
   88   | Sam       | [email protected]

现在是否可以使用 ASJHD2 获取参与消息的所有不同电子邮件。请记住，用户表上还需要有一个联接。

原文

This one has stumped me.

A quick overview

I need to be able to get all email addresses of people who have participated in a message thread. People can add messages to this messageThread via a few different means, and because people can either be the sender or receive a message, the email addresses might be in different columns.

Here is an example table.

messagesTable

ID | threadKey |  fromEmail   |  toEmail    | sentBy   |   sentTo
1  | ASJHD2    | [email protected]  | [email protected] |  null    |   null
2  | ASJHD2    | [email protected]   |             |  null    |   null
3  | ASJHD2    |              |             |  55      |   88

I also have my Users table.

   ID   | username  | email
   55   | Ike       | [email protected]
   88   | Sam       | [email protected]

Now is it possible to get all of the distinct emails that have participated in the message with a threadKey of ASJHD2. Keeping in mind that there would also need to be a join on the users table.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

紫罗兰の梦幻 2024-12-20 13:49:05

首先尝试只获取电子邮件，例如

SELECT fromEmail
FROM messagesTable
WHERE fromEmail IS NOT NULL
UNION
SELECT toEmail
FROM messagesTable
WHERE toEmail IS NOT NULL

First try to get just emails like

SELECT fromEmail
FROM messagesTable
WHERE fromEmail IS NOT NULL
UNION
SELECT toEmail
FROM messagesTable
WHERE toEmail IS NOT NULL

回复收藏 0 原文

暮色兮凉城 2024-12-20 13:49:05

您需要做的本质上是联接到用户表，但说明使用任一字段进行联接的条件。然后，您可以根据您要查找的电子邮件地址对结果进行分组。

SELECT * FROM messagesTable AS m
JOIN Users AS u ON u.email = m.fromEmail OR u.email = m.toEmail
WHERE m.threadKey = 'ASJHD2'
GROUP BY u.email;

What you need to do is essentially join to your users table but stating the condition to use either field for the join. Then you group the results based on the email addresses that you're looking for.

SELECT * FROM messagesTable AS m
JOIN Users AS u ON u.email = m.fromEmail OR u.email = m.toEmail
WHERE m.threadKey = 'ASJHD2'
GROUP BY u.email;

回复收藏 0 原文

爱你是孤单的心事 2024-12-20 13:49:05

仅供有兴趣的人使用。我使用了 Max 答案并添加了加入。这是我最终得到的最终查询。

SELECT fromEmail as email FROM messages m WHERE fromEmail IS NOT NULL and m.threadKey = "86ec8125-988d-478f-8b1c-8c1a7c3056f5"
UNION SELECT toEmail FROM messages m WHERE toEmail IS NOT NULL and m.threadKey = "86ec8125-988d-478f-8b1c-8c1a7c3056f5"
UNION SELECT email  from messages m JOIN acl_users on sentBy =  acl_users.id where m.threadKey = "86ec8125-988d-478f-8b1c-8c1a7c3056f5"
UNION SELECT email  from messages m JOIN acl_users on sentTo =  acl_users.id where m.threadKey = "86ec8125-988d-478f-8b1c-8c1a7c3056f5"

Just for anyone interested. I used Max answer and added the Join also. This is the final query that I ended up with.

SELECT fromEmail as email FROM messages m WHERE fromEmail IS NOT NULL and m.threadKey = "86ec8125-988d-478f-8b1c-8c1a7c3056f5"
UNION SELECT toEmail FROM messages m WHERE toEmail IS NOT NULL and m.threadKey = "86ec8125-988d-478f-8b1c-8c1a7c3056f5"
UNION SELECT email  from messages m JOIN acl_users on sentBy =  acl_users.id where m.threadKey = "86ec8125-988d-478f-8b1c-8c1a7c3056f5"
UNION SELECT email  from messages m JOIN acl_users on sentTo =  acl_users.id where m.threadKey = "86ec8125-988d-478f-8b1c-8c1a7c3056f5"

回复收藏 0 原文

~没有更多了~