加入三个表（链接表）(Joining three tables (link table))

加入三个表（链接表）(Joining three tables (link table))

需要帮助我写的查询：

我有三张桌子

公司

id name 1 Gary's

雇员

id name company_id 1 Tim Jones 1 2 Sam Adams 1

向...报告

employee_id reports_to_id 1 2

我目前的查询是：

select temp.company.name as comp_name, temp.employee.name as employee_name, temp.employee.id as employee_id from temp.employee, temp.employee where temp.company.id = temp.employee.company_id and temp.company.id = 1

这给了我输出：

comp_name employee_name employee_id Gary's Tim Jones 1

我需要这样的东西：

comp_name employee_name reports_to Gary's Tim Jones Sam Adams

修改我的查询以执行此操作的好方法是什么？ 我有一个查询然后我采取这些结果并对该结果集运行第二个查询（这是非常不必要的）。

Need help with a query that I wrote:

I have three tables

Company

id name 1 Gary's

Employee

id name company_id 1 Tim Jones 1 2 Sam Adams 1

reports to

employee_id reports_to_id 1 2

My current query is:

select temp.company.name as comp_name, temp.employee.name as employee_name, temp.employee.id as employee_id from temp.employee, temp.employee where temp.company.id = temp.employee.company_id and temp.company.id = 1

Which gives me the output of:

comp_name employee_name employee_id Gary's Tim Jones 1

I need something like this:

comp_name employee_name reports_to Gary's Tim Jones Sam Adams

What's a good way to modify my query to do this? I have a query and then I take those results and run a second query against that result set (which is excessively unnecessary).

最满意答案

假设一名员工只向一个人报告，那么我们可以（没有链接表）

Employee (Id, Name, CompanyId, ReportsToId) Company (Id, Name)

然后你可以有类似的查询

select e.Name EmployeeName, c.Name CompanyName, r.Name ReportsTo from Employee e inner join Company c on e.CompanyId = c.Id inner join Employee r on e.ReportsToId = r.Id where e.CompanyId = 1

如果员工向多人报告，那么我们将使用链接表

Employee (Id, Name, CompanyId) EmployeeReportsTo (EmployeeId, ManagerId) Company (Id, Name) select e.Name EmployeeName, c.Name CompanyName, r.Name ReportsTo from Employee e inner join Company c on e.CompanyId = c.Id inner join EmployeeReportsTo ert on ert.EmployeeId = e.Id inner join Employee r on ert.ManagerId = r.Id where e.CompanyId = 1

Assuming an employee only reports to one person then we could have (no link table)

Employee (Id, Name, CompanyId, ReportsToId) Company (Id, Name)

Then you could have a query similar to

select e.Name EmployeeName, c.Name CompanyName, r.Name ReportsTo from Employee e inner join Company c on e.CompanyId = c.Id inner join Employee r on e.ReportsToId = r.Id where e.CompanyId = 1

If the employee reports to multiple people then we would use a link table

Employee (Id, Name, CompanyId) EmployeeReportsTo (EmployeeId, ManagerId) Company (Id, Name) select e.Name EmployeeName, c.Name CompanyName, r.Name ReportsTo from Employee e inner join Company c on e.CompanyId = c.Id inner join EmployeeReportsTo ert on ert.EmployeeId = e.Id inner join Employee r on ert.ManagerId = r.Id where e.CompanyId = 1