Results 1 to 2 of 2

Thread: Subqueries?

  1. #1
    Join Date
    Feb 2014

    Unanswered: Subqueries?

    I have a mySQL database with 3 tables - matters, actions, expenses. The relevant fields in these are:

    Matters - matterid, mattername, active
    Actions - actionid, timespent, fee, matterid, billed
    Expenses - expenseid, expenses, matterid, billed

    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‘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.

    Can anyone offer some help/guidance?

  2. #2
    Join Date
    Dec 2007
    Richmond, VA
    Provided Answers: 5
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts