SQL for a many to many relationship using inner joins

Question

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.

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

Answer 1

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

Answer 2

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