querying multiple tables

[todraven]

Hi All

I am trying to make a spending sheet for a project management database.

I've got Cost Codes e.g A1=300, A2=500 in their own table and the projects in a table with each project assigned a cost code.

Basically what i want to achieve is a page that outputs the total cost

So, get a value from the Cost Code table then seeing how many times it occurs in the projects table, multiplying it, then repeating the process for each different cost code, and then display the total.

Any pointers would be appreciated.

Many Thanks

[nemonoman]

I think you want to do a join with 2 group bys

The Join starts out

SELECT * FROM costcodes, projects where costcode.id=procect.costcodeid

You want to group the projects together

SELECT * FROM costcodes, projects where costcode.id=procect.costcodeid
GROUP BY project.id

You also then want go group the costs together
GROUP BY project.id, costcode.id

You dont want all the fileds, but You want
project.id, costcode.id

and some math--

How many times the costcode id occurs tiimes the cost amount

COUNT(costcode.id) * costcode.amount

SO I think it's:

SELECT project.id,
costcode.id,
COUNT(costcode.id) * costcode.amount
FROM costcodes, projects
WHERE costcode.id=procect.costcodeid
GROUP BY project.id, costcode.id