JOIN, GROUP BY, or HAVING question

Consider the following two tables:

ProjectHours
hoursID {primary key}
employeeID {foreign key}
ProjectDate
ProjectID
ProjectHours

DataComplete
DataCompleteID {primary key}
employeeID {foreign key}
CompleteDate

The first table should be self-explanatory. The second table is there to let me know that a particular employee has entered all of the data for a given date. This tells me that I can include this data in reports and charts.

Here's where I'm having troubles ...

I want to calculate the average hours worked by an employee on a project during a given time period. For example, what is the average number of hours John worked on Project X during the past week? The tricky part is that the employee/date combo must also be found in the
DataComplete table.

Problem: The following SQL statement averages ALL of the data even for dates NOT included in the DataComplete table:

SELECT
AVG(h.ProjectHours) AS avg_hours
FROM
ProjectHours h
JOIN
DataComplete d
ON
h.employeeID = d.employeeID
WHERE
d.employeeID = 123
AND d.CompleteDate >= '7/26/2003'
AND d.CompleteDate <= '8/1/2003'
AND h.ProjectID = 8

How do I get aggregate info only for dates found in the DataComplete table?I see you have a primary key on your ProjectHours table, but what is the NATURAL key?

Do employeeID, ProjectDate, and ProjectID constitute a unique records? If so, try this:

SELECT
AVG(h.ProjectHours) AS avg_hours
FROM
ProjectHours h
INNER JOIN DataComplete d
ON h.employeeID = d.employeeID
AND h.ProjectDate = d.CompleteDate
WHERE
d.employeeID = 123
AND d.CompleteDate >= '7/26/2003'
AND d.CompleteDate <= '8/1/2003'
AND h.ProjectID = 8

If this is not the case, I suspect you will need to modify (normalize) your table design to do what you want to do.

blindman