So a matter would have recorded against it a number of actions and may have a number of expenses. In matters ‘active’ can be Y or N and ‘billed’ in actions and expenses can be Y or N.
What I wish to do is have a table which list all matters where active=Y and, alongside that mattername, list a total sum of (timespent*fee) where actions.billed = N and also a sum of expenses where expenses.billed=N.
I have tried this:
SELECT matters.mattername, SUM(actions.timespent*actions.fee) AS totfee, SUM(expenses.expense) AS totexp
FROM actions INNER JOIN matters ON actions.matterid=matters.matterid LEFT JOIN expenses ON actions.matterid=expenses.matterid
WHERE (actions.billed = 'N' OR expenses.billed='N') AND matters.active=‘Y’
GROUP BY actions.matterid
Which doesn’t work. I think it’s something to do with subqueries but can’t seem to get the syntax right.
what do you mean doesn't work? For all the info you gave us we could say that to resolve it doesn't work you have to stand on one foot on Wednesday with your tongue sticking out to the right. Also, hard to say it has anything to do with subqueries, since you have none in your SQL. Never put conditions on outer joined table in a WHERE clause, they should only be in the ON clause.
Other than that the answer can be found on page 193.