SQL Server：使用左连接从两个表中选择一列作为单个列

发布于 2024-10-13 10:00:56 字数 775 浏览 6 评论 0原文

我正在尝试构建一个选择查询，该查询本质上将左连接两个表并将每个表中的一列显示为单列。

表结构类似于：

表 a:

id, email

表 b:

id, tablea_id, email

我试图获取电子邮件和电子邮件的单列（理想情况下没有重复或空值）。

理想的结果是：

[email protected]
[email protected]
[email protected]

返回的电子邮件地址可能来自 a 或 b。

也许联合是最有效的，但我无法弄清楚如何根据第一个表的 id 在第二个表上进行联合。

在寻找解决方案时，也许我的措辞不好，但我找不到任何例子。

感谢您的帮助。

原文

I am trying to build a select query that will essentially left join two tables and display one column from each table as a single column.

the table structure is similar to:

table a:

id, email

table b:

id, tablea_id, email

I am trying to get a single column of email and email (with no dupes or nulls ideally).

ideal results would be:

[email protected]
[email protected]
[email protected]

and the email address that is returned could be from either a or b.

Maybe a union is what would work best, but I not able to figure out how to do a union on the second table based on the id of the first table.

When searching for a solution, perhaps my wording is bad, but I can't find any examples.

thanks for any help.

分享到QQ

分享到微博

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

发布评论

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

ζ澈沫 2024-10-20 10:00:56

看来你想要这样的东西：

SELECT email
FROM TableA
UNION
SELECT B.email
FROM TableA A
JOIN TableB B
ON A.id = B.TableA_id

It seems that you want something on the lines of this:

SELECT email
FROM TableA
UNION
SELECT B.email
FROM TableA A
JOIN TableB B
ON A.id = B.TableA_id

回复收藏 0 原文

冷心人i 2024-10-20 10:00:56

如果您只需要任一表中的电子邮件地址的唯一列表，您可以执行以下操作：

Select email
From TableA
Union
Select email
From TableB

：

Select TableA.email
From TableA
    Join TableB
        On TableB.TableA_id = TableA.id
Union
Select email
From TableB

如果您正在查找表 B 中的电子邮件地址以及表 A 中存在于表 B 中的电子邮件地址的唯一列表，那么您可以执行以下操作根据您的评论，您需要表 A 中的所有行，并且只需要表 B 中存在于表 A 中的行：

Select email
From Table A
Union 
Select TableB.email
From TableB
    Join TableA
        On TableA.id = TableB.TableA_id

If you simply want a unique list of email addresses from either table you can do:

Select email
From TableA
Union
Select email
From TableB

If you are looking for a unique list of email addresses from Table B and those from Table A that exist in Table B, then you can do:

Select TableA.email
From TableA
    Join TableB
        On TableB.TableA_id = TableA.id
Union
Select email
From TableB

If, per your comments, you need all rows from Table A and only rows from Table B where they exist in Table A:

Select email
From Table A
Union 
Select TableB.email
From TableB
    Join TableA
        On TableA.id = TableB.TableA_id

回复收藏 0 原文

亽野灬性zι浪 2024-10-20 10:00:56

这个怎么样：

SELECT DISTINCT a.email+ ' ' + b.email FROM tableA a LEFT JOIN tableB b on a.Id = b.tablea_Id WHERE b.email IS NOT NULL

How about this:

SELECT DISTINCT a.email+ ' ' + b.email FROM tableA a LEFT JOIN tableB b on a.Id = b.tablea_Id WHERE b.email IS NOT NULL

回复收藏 0 原文

月野兔 2024-10-20 10:00:56

这可以帮助：

SELECT
    id, email
FROM
    a

UNION

(
    SELECT
        a.id AS id, b.email AS email
    FROM
        b
    INNER JOIN
        a
    ON
        a.id = b.tablea_id
) b

This could help:

SELECT
    id, email
FROM
    a

UNION

(
    SELECT
        a.id AS id, b.email AS email
    FROM
        b
    INNER JOIN
        a
    ON
        a.id = b.tablea_id
) b

回复收藏 0 原文

~没有更多了~