Joing multiple with one master table
I have six tables- Project,Equipment,Fish,Staff and the junction tables -
Project_Equipment,Project_Fish and Project_Staff.I want to retrieve
Project total cost.
So, I wrote the statement like follows,
SELECT P.ProjectID,
(SUM(E.EquipPrice*PE.EQuantity)+SUM(F.FishPrice*PF.FQuantity)+SUM(PS.Salary))
as ProjectCost
FROM Equipment as E INNER JOIN Project_Equipment as PE
ON E.EquipID=PE.EquipID
INNER JOIN Project as P
ON PE.ProjectID=P.ProjectID
INNER JOIN Project_Fish as PF
ON P.ProjectID=PF.ProjectID
INNER JOIN Fish as F
ON PF.FishID=F.FishID
INNER JOIN Project_Staff as PS
ON P.ProjectID=PS.ProjectID
INNER JOIN Staff as S
PS.StaffID=S.StaffID
GROUP BY ProjectID
But, I got the price with twice of correct amount.
No comments:
Post a Comment