Multi table query

[Guest]

Hello

I have a database for project and resources that has four tables and the
fields:
ResourceID, ResourceName, Cost
fkResourceID, fkSubprojectID, TaskName
SubProjectID, fkProjectID, SubProjectName
ProjectID, ProjectName
The rules are:
-A project can have one or many subprojects.
-A subproject can have one or many tasks.
-A resource can be used in tasks in one or many subproject of a project.
-A task is found once in a subproject.
-A resource is charged once for a project no matter if it has used in many
of its subprojects.
Can you please suggest how can I make a query to calculate the resources
cost for each project?

Thank you

GL

 

[Tom Ellison]

Dear GL:

Let's start at the top.

You have 4 tables. Are they: Project, SubProject, Resource, and Task?

Now, how are they related? It sounds like:

Project (one) to SubProject (many)
SubProject (one) to Task (many)
Resource (one) to Task (many)

Do you understand what this means, and very specifically?

Now, the columns you mention: In which tables are they found?

From the aboive, I would guess:

ProjectID (and fkProjectID) is used in the relationship Project to
SubProject.
SubProjectID (and fkSubProjectID) is used in the relationship SubProject to
Task.
ResourceID (and fkResourceID) is used in the relationship Task to Resource.

Now if all the above holds, then the query might be:

SELECT P.ProjectID, SUM(R.Cost) AS ProjectCost
FROM Project P
INNER JOIN (SubProject S
ON S.fkProjectID = P.ProjectID
INNER JOIN Resource R
ON R.fkSubProjectID = S.SubProjectID)
GROUP BY P.ProjectID

From what I could see (and guess) it does not seem Task is involved in
obtaining cost.

This is actually based on a fair amount of guesswork, due to lack of
details. If you provide more details, there would be less guesswork and
possibly more accuracy.

Tom Ellison

 

[Guest]

Dear Tom

I am sorry I have not given more details but I used typical terms for field
naming that could be self explanatory of the db.
Your guesswork was very precise. The structure of Tables and Relations is
exactly as you have mentioned. However the query you suggest does not give a
result.

If you mean instead something like the query below, which is like yours but
with the write relations description, it gives wrong results. The fault is
that if a resource, lets say, is used on three subprojects of a project, it
isn’t counted once as it should be according to the rules but three times.

SELECT Projects.ProjectID, Sum(Resources.Cost) AS SumOfCost
FROM Resources INNER JOIN ((Projects INNER JOIN Subprojects ON
Projects.ProjectID = SubProjects.fkProjectID) INNER JOIN Tasks ON
Subprojects.SubprojectID = Tasks.fkSubProjectID) ON Resources.ResourceID =
Tasks.fkResourceID
GROUP BY Projects.ProjectID;

 

[Tom Ellison]

Dear GL:

I suggest, then, a two part query scheme. Query to get a list of Resourses
and their costs for each project, and add DISTINCT to that. Then SUM the
resource costs based on that. Something like:

SELECT DISTINCT P.ProjectID, R.ResourceID, R.Cost
FROM Resources
INNER JOIN ((Projects P
INNER JOIN Subprojects S
ON P.ProjectID = S.fkProjectID)
INNER JOIN Tasks
ON S.SubprojectID = T.fkSubProjectID)
ON R.ResourceID = T.fkResourceID

Do your totals query on this.

Tom Ellison