使用特定列连接两个表

I am new to SQL, I know this is really basic but I really do not know how to do it! I am joining two tables, each tables lets say has 5 columns, joining them will give me 10 columns in total which I really do not want. What I want is to select specific columns from both of the tables so that they only show after the join. (I want to reduce my joining result to specific columns only)

我是SQL的新手,我知道这是非常基本但我真的不知道怎么做!我正在加入两个表,每个表让我们说有5列,加入它们会给我10列,我真的不想要。我想要的是从两个表中选择特定列,以便它们仅在连接后显示。 (我想仅将连接结果减少到特定列)

SELECT * FROM tbEmployees

JOIN tbSupervisor

ON tbEmployees.ID = tbSupervisor.SupervisorID

The syntax above will give me all columns which I don't want. I just want EmpName, Address from the tblEmployees table and Name, Address, project from the tbSupervisor table

上面的语法将为我提供我不想要的所有列。我只想要来自tblEmployees表的EmpName,Address和来自tbSupervisor表的Name,Address,project

I know this step:

我知道这一步:

SELECT EmpName, Address FROM tbEmployees

JOIN tbSupervisor

ON tbEmployees.ID = tbSupervisor.SupervisorID

but I am not sure about the supervisor table.

但我不确定主管表。

I am using SQL Server.

我正在使用SQL Server。

6 个解决方案

#1

This is what you need:

这就是你需要的:

Select e.EmpName, e.Address, s.Name, S.Address, s.Project
From tbEmployees e
JOIN tbSupervisor s on e.id = SupervisorID

However, I would definitely suggest reading up some more on SQL. http://www.w3schools.com/sql/default.asp is a decent place to start.

但是,我肯定会建议在SQL上阅读更多内容。 http://www.w3schools.com/sql/default.asp是一个不错的起点。

#2

You can get columns from specific tables, either by their full name or using an alias:

您可以通过其全名或使用别名从特定表中获取列:

SELECT E.EmpName, E.Address, S.Name, S.Address, S.Project
FROM tbEmployees E
INNER JOIN tbSupervisor S ON E.ID = S.SupervisorID

#3

You can use the table name as part of the column specification:

您可以将表名用作列规范的一部分:

SELECT tbEmployees.EmpName, tbEmployeesAddress, tbSupervisor.Name,
       tbSupervisor.Address, tbSupervisor.project

FROM tbEmployees

JOIN tbSupervisor

ON tbEmployees.ID = tbSupervisor.SupervisorID

#4



    SELECT employees.EmpName, employees.Address AS employeer address, 
           supervisor.Name, supervisor.Address AS supervisor address,supervisor.project 
    FROM tbEmployees 
       AS employees 
    JOIN tbSupervisor 
       AS supervisor 
    ON 
       employees.ID = supervisor.SupervisorID

#5

You need to learn about aliases. They will make your queries more maintainable. Also, you should always use aliases when referencing columns, so your query is clear about what it is doing:

您需要了解别名。它们将使您的查询更易于维护。此外,在引用列时应始终使用别名,因此您的查询清楚地知道它在做什么:

SELECT e.EmpName, e.Address, s.name, s.address as SupervisorAddress
FROM tbEmployees e  JOIN
     tbSupervisor s
     ON e.ID = s.SupervisorID;

Note that I also renamed the second address so its name is unique.

请注意,我还重命名了第二个地址,因此它的名称是唯一的。

#6

Specify the table name and field name in your selection

在您的选择中指定表名称和字段名称

SELECT tbEmployees.EmpName,
       tbEmployees.Address,
       tbSupervisor.[column name]
  FROM tbEmployees
  JOIN tbSupervisor ON tbEmployees.ID = tbSupervisor.SupervisorID

#1