mysql - Joing multiple with one master table -

i have 6 tables- project,equipment,fish,staff , junction tables - project_equipment,project_fish , project_staff.i want retrieve project total cost.
so, wrote statement follows,

select p.projectid, (sum(e.equipprice*pe.equantity)+sum(f.fishprice*pf.fquantity)+sum(ps.salary)) projectcost   equipment e inner join project_equipment pe   on e.equipid=pe.equipid   inner join project p   on pe.projectid=p.projectid   inner join project_fish pf   on p.projectid=pf.projectid   inner join fish f   on pf.fishid=f.fishid   inner join project_staff ps   on p.projectid=ps.projectid   inner join staff s ps.staffid=s.staffid   group projectid   

but, got price twice of correct amount.

your query end lot of duplicate results. consider following simpler case:

table: project projectid  equantity   1          1   2          1  table: equipment equipid    eprice   1          1   2          1  table: fish fishid   1   2  table: project_equipment projectid  equipid   1          1   1          2   2          1   2          2  table: project_fish projectid  fishid   1          1    1          2   2          1   2          2 

now, let's @ results of project_equipment query only:

select p.projectid, e.eprice, pe.equantity project p inner join project_equipment pe on pe.projectid=p.projectid inner join equipment e on e.equipid=pe.equipid  projectid eprice equantity 1    1    1   // 1    1    1   // b 2    1    1   // c 2    1    1   // d 

that's expected; list of price , quantity of each piece of equipment used each project. think happen when inner join that project_fish? first result has project 1 in twice, , project 2 in twice, end every combination of result , project_fish!

select p.projectid, e.eprice, pe.equantity, f.fishid project p inner join project_equipment pe on pe.projectid=p.projectid inner join equipment e on e.equipid=pe.equipid inner join project_fish pf on pf.projectid=p.projectid  projectid eprice equantity fishid 1    1    1    1   // above 1    1    1    2   // above 1    1    1    1   // b above  1    1    1    2   // b above 2    1    1    1   // c above 2    1    1    2   // c above 2    1    1    1   // d above 2    1    1    2   // d above 

this duplication continue every inner join. amount price off won't 2x, depend on number of combinations of joins.

so, can't trying particular query. instead you'll have calculate cost of each relationship separately. sum together. can selecting each 1 separately , calculating cost projectid , projectcost column, using union concatenate altogether, once again grouping results projectid , summing individual projectcost subtotals.

i explained poorly, think of subtotaling equipment, fish, , salary costs, sticking subtotals 1 table , summing that. e.g.:

select x.projectid, sum(x.projectcost)  (     select p.projectid, sum(e.eprice * pe.equantity) projectcost project p     inner join project_equipment pe on pe.projectid=p.projectid     inner join equipment e on e.equipid=pe.equipid     group p.projectid   union     select p.projectid, sum(f.fprice * pf.fquantity) projectcost project p     inner join project_fish pf on pf.projectid=p.projectid     inner join fish f on f.fishid=pf.fishid     group p.projectid   union     select p.projectid, sum(s.salary) projectcost project p     inner join project_staff ps on ps.staffid=p.projectid     inner join staff s on s.staffid=ps.staffid     group p.projectid ) x group x.projectid 

each of subqueries produces projectid column , projectcost column. run subquery (between parens) see results. outer query adds subtotals projects.

sorry, btw, renamed equipprice , fishprice columns eprice , fprice when testing.