sub query in having clause?

[sunny124]

Hi,

I'm trying write a sql statement that will:
show all employee id , name, their wages and the project they are doing if their wage's are greater than average wage.

The wage is calculated by the hourly rate which is located in allocation table and hrsworked which is located in timelog table.

allocation table is a many to many representation of the relationship between employee and project.

The error msg I am getting is:
"A"."PROJID": invalid identifier referring to the A.ProjID in the subquery.

This is what I have done so far:

Can anyone help out as to what I am doing wrong.

Thanks.

sql code:

SELECT E.EmpID, E.EmpName, P.Description as "Project Description", SUM(A.HourlyRate * T.HrsWorked) AS "Total Pay"
FROM EMPLOYEE E
INNER JOIN ALLOCATION A
ON A.EmpID = E.EmpID
INNER JOIN PROJECT P
ON A.ProjID = P.ProjID
INNER JOIN TIMELOG T
ON A.ProjID = T.ProjID AND A.EmpID = T.EmpID
GROUP BY E.EmpID, E.EmpName, P.Description
HAVING SUM(A.HourlyRate * T.HrsWorked) >  (SELECT avg(A.HourlyRate * T.HrsWorked)
FROM ATTENDANCE A INNER JOIN TIMELOG T ON A.ProjID = T.ProjID AND A.EmpID = T.EmpID											);

[hfx642]

This is because you are using "A" for Allocation AND you are using "A" for Attendance.
When you go to run your query, it gets confused in your "A"s.
ie. There is no Attendance.ProjID
Use a different table alias for one of these tables.