如何在SQL Server中的两个不相关的表中获取匹配的数据？ [英] How to get the matched data in two unrelated tables in SQL Server?

问题描述

大家好，



我有两个不相关的表，没有任何主键或关系如下：

Hello everybody,

I have two unrelated tables without any primary key or relationship as following:

Employee Table: ID, Dispaly Name, Name, Department
Participants Table: User Name, Name

这是一个糟糕的数据库设计，但因为我将它们从Excel工作表导入到SQL Server Management Studio。我知道两者中[显示名称]和[用户名]列之间有很多匹配，所以我想显示匹配的部门列。 怎么做？



我想出了这个查询，但它没有告诉我任何结果，我不知道原因：

This is a bad database design but because I imported both of them from Excel sheet to SQL Server Management Studio. I know there are many matches between [Display Name] and [User Name] columns in both of them, so I want to show the department column for the matches. How to do that?

I came up with this query, but it did not show me any results and I don''t know why:

SELECT dbo.[Participants].[User Name], dbo.Employee.Department
    FROM 
    Employee, ['Participants']
     WHERE   Employee.[Display name] = ['Participants'].[User Name]

推荐答案

试试这个：

Try this:

SELECT P.[User Name], E.Department
FROM [Employee] AS E
INNER JOIN Participants AS P
ON E.[Display name] = P.[User Name]

[ ''参与者''] 不是写表名的正确方法。

而不是写 [参与者] 或仅参与者。



因此，查询看起来像......

[''Participants''] is not the correct way to write the table name.
Instead write like [Participants] or only Participants.

So, the query will look like...

SELECT
    dbo.[Participants].[User Name],
    dbo.Employee.Department
FROM
    Employee, [Participants]
WHERE
    Employee.[Display name] = [Participants].[User Name]

或者您将使用 INNER JOIN ，然后查询就像...

Or if you will use INNER JOIN, then query will be like...

SELECT 
    Part.[User Name], 
    emp.Department
FROM 
    Employee AS emp 
INNER JOIN 
    [Participants] AS Part
ON   
    emp.[Display name] = Part.[User Name]

谢谢......

Thanks...

这篇关于如何在SQL Server中的两个不相关的表中获取匹配的数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！