SQL Query Help

[jman27]

I'll explain the problem I'm running into throughout below. To keep things simple I'll be stripping the tables to the bare necessities for my explanation.

We have a project tracking system our employees are required to fill out each day. They log in, select the project they worked on, and enter the number of hours they worked on it. Managers then run reports, for budgetary reasons, that show them the cost of each project. I fetch the report in a simple query that goes through all the projects (Projects table), joins each project up with their rate (Rates table), and sums up the cost for each project. The tricky part now is a manager may set a specific rate for an employee working on project X. Therefore when the query runs it must use a different rate (from the Rates table) for the given project / employee.

The calculation I'm using in the query is a basic: sum(RATE * HOURS)

A quick summary of the table relationships:
All projects have a "default" rate associated with them and thus will have 1 entry in Rates for each project. Any additional entries in Rates for a particular project will be because an empID was given to change the rate for a particular employee for that project.

The query I'm currently using to fetch a list of projects and their "default" costs:

SELECT p.projID, p.projName, sum(r.rate * ph.hours) AS totalCost
FROM Projects p, ProjectHours ph, Rates r
WHERE p.projID=r.projID
GROUP BY p.projID, p.projName

Now I just need to throw in the twist of an specific employee having a different rate then the "default" rate for a particular project. Any help would be greatly appreciated. The creation of a View is acceptable... but I'd rather see if this is possible using some combination of JOINS.

Tables below...

Employees (snipped)
----------------------
empID
empName


Projects (snipped)
----------------------
projID
projName


ProjectHours (snipped)
----------------------
empID
projID
hours


Rates (snipped)
----------------------
projID (not null)
empID (will be null for the "default" project rate)
rate

 

[rtmpxr]

SELECT p.projID, p.projName, sum(r.rate * ph.hours) AS totalCost
FROM Projects p, ProjectHours ph, Rates r, Employees e
WHERE p.projID=r.projID and p.projID=ph.projID and e.empID=ph.empID and e.empID=r.empID
GROUP BY p.projID, p.projName