SELECT workgroups.*,employees_subselect.workgroup_id
FROM workgroups
JOIN (SELECT employees.workgroup_id,avg(salaries.salary)
FROM employees
JOIN salaries USING(emp_id)
GROUP BY employees.workgroup_id) AS employees_subselect
USING (workgroup_id)
Actual: The inner join bleeds out of the subselect, also a field is double - selected:
SELECT workgroups.*,employees_subselect.workgroup_id,employees_subselect.workgroup_id
FROM workgroups
JOIN (SELECT employees.workgroup_id,avg(salaries.salary)
FROM employees
JOIN salaries USING(emp_id)
GROUP BY employees.workgroup_id) AS employees_subselect
USING (workgroup_id)
JOIN salaries USING(emp_id)
Join two tables, aggregate over the join, join onto another table:
Expected: Something like
Actual: The inner join bleeds out of the subselect, also a field is double - selected: