使用内部联接的多对多关系的 SQL

SQL for a many to many relationship using inner joins

提问by DBurton

I have the following many-to-many relationship between employees and workgroups:

我在员工和工作组之间有以下多对多关系：

employees table
-----------------
id

empgroups table
---------------
employee_id
workgroup_id

workorders table
----------------
workgroup_id

I'm trying to write SQL that will list all the workorders for an employee based on the workgroups that employee belongs to.

我正在尝试编写 SQL，该 SQL 将根据员工所属的工作组列出该员工的所有工单。

This is my attempt:

这是我的尝试：

SELECT wonum, workgroup_id
FROM workorders
INNER JOIN employees
ON workorders.employee_id = employee_id
INNER JOIN empgroups 
ON employees.employee.id = empgroups.employee_id
WHERE employee_id = 2

The error I get is:

我得到的错误是：

ERROR:  schema "employees" does not exist

Sorry - the employee has id not employee.id

抱歉 - 员工的 ID 不是 employee.id

回答by Denis de Bernardy

Isn't this what you're looking for?

这不是你要找的吗？

SELECT wonum, workgroup_id
  FROM workorders
  JOIN empgroups 
    ON empgroups.workgroup_id = workorders.workgroup_id
  JOIN employees
    ON employees.employee_id = empgroups.employee_id
 WHERE employees.employee_id = 2

回答by Erwin Brandstetter

SELECT w.wonum, w.workgroup_id
FROM   workorders w
JOIN   empgroups  e USING (workgroup_id)
WHERE  e.employee_id = 2

The table employeesis not needed at all for this query. USINGshortens the syntax in this case. As do table aliases.

employees此查询根本不需要该表。USING在这种情况下缩短语法。表别名也是如此。

回答by SOfanatic

Try using this query instead:

尝试改用此查询：

SELECT * FROM empgroups
INNER JOIN employees
ON empgroups.empId = employees.id
INNER JOIN workorders
ON empgroups.woId = workorders.id

SqlFiddle

SqlFiddle