Hi!
SELECT
e.emp_no
FROM employee e
JOIN employee_project p ON p.emp_no = e.emp_no
GROUP BY e.emp_no
HAVING MAX((SELECT
SUM(e2.salary)
FROM employee e2
JOIN employee_project p2 ON p2.emp_no = e2.emp_no
WHERE e2.emp_no = e.emp_no AND
p2.proj_id IS NOT DISTINCT FROM p.proj_id)) <> 0
This is accepted by Firebird (also by Oracle).
In the subselect p.proj_id should not be accepted - group does not contains
it, and has no aggregate on it. What value the engine choose when it runs, and
why?
Is this equivalent with it?
SELECT
e.emp_no
FROM employee e
JOIN employee_project p ON p.emp_no = e.emp_no
GROUP BY e.emp_no
HAVING (SELECT
SUM(e2.salary)
FROM employee e2
JOIN employee_project p2 ON p2.emp_no = e2.emp_no
WHERE e2.emp_no = e.emp_no AND
p2.proj_id IS NOT DISTINCT FROM MAX(p.proj_id)) <> 0
