MYSQL: Multiple joins

I am trying to put together a simple budget and expense tracking tool with three tables.

Very simply, there is a budget table which includes the name of the budget and the budget amount.

There is a table holding commitments, where each commitment has a number (for referencing), a amount and the name of the budget it is held against.

The third table holds details of actual expenses, with the name of the budget the expense is held against and the expense amount. If the expense is linked to a commitment, then the commitment number can also be used.

In summary, the rules are:

• every commitment must be held against a budget, although not every budget will have commitments,
• every expense must be held against a budget,
• not all expenses are held against a commitment,
• a budget might have expenses but no commitments,
• a commitment might not yet have an expense posted against it, and
• all amounts are summar开发者_如何学Goised.

The result I am trying to produce is a report showing each budget, the total of expenses posted to it and the total of (commitments less expenses posted to the commitment) posted to that budget.

I can (1) link a budgets to expenses or (2) link budgets to commitments. But I can't link all three to produce the right result - I end up with huge sums because every expense is shown against every commitment.

I've looked at lots of the examples here (and will keep looking) but understanding the actual workings of what is presented is a bit beyond me right now.

Instead of someone putting together a fully worked solution (which I don't really expect), can someone provide a simple explanation as to how to get the following results:

• Table 1: Budgets -> every row to appear
• Table 2: Expenses -> summed by budget, where expenses exist for the budge
• Table 3: Remaining commitment -> summed by budget, where a commitments exists AND less expenses where expenses have been posted to the commitment.

I'm happy to post my database structures, but frankly I've been working for this on hours and there's not much to show as yet!

[EDIT] The final result should look like (using the sample structure suggested by mellamokb):

Budget item    Budget amount     Expenses     Remaining commitment
IT             5000              390          770 
Admin          1000              250          20
Tech           300               100          0
Development    9000              350          0

Using the following DB structure that I have modelled from your description (I realize this structure is not normalized - I'm trying as best as possible to mimic the description given by OP):

• Budget: ID (PK), Name, Amount
• Commitment: ID (PK), RefNumber, Budget, Amount
• Expense: ID (PK), Budget, Amount, RefNumber (can be NULL)

Here is the sample data I am using for the following examples (in thousands).

Budget:

ID  Name        Amount
1   IT          5000
2   Admin       1000
3   Tech        300
4   Development 9000

Commitment:

ID  RefNumber   Budget  Amount
1   SYSUPGRADE  IT      750.00
2   PHONES      Admin   35.00
3   WINDOWS7    IT      300.00

Expense:

ID  Budget      Amount      RefNumber
1   IT          35.00       NULL
2   IT          65.00       NULL
3   IT          125.00      SYSUPGRADE
4   IT          80.00       SYSUPGRADE
5   Tech        25.00       NULL
6   Tech        75.00       NULL
7   Admin       90.00       NULL
8   Development 300.00      NULL
9   Development 50.00       NULL
10  Admin       10.00       PHONES
11  Admin       5.00        PHONES
12  Admin       12.00       NULL
13  Admin       133.00      NULL
14  IT          25.00       WINDOWS7
15  IT          60.00       WINDOWS7

Here are the queries that would satisfy your three Table examples.

Table 1: Budgets -> every row to appear

select
    Name, Amount
from
    Budget B

Output:

Name        Amount
IT          5000.00
Admin       1000.00
Tech        300.00
Development 9000.00

Table 2: Expenses -> summed by budget, where expenses exist for the budge

select
    Budget, sum(Amount) As Amount
from
    Expense E
group by
    Budget

Output:

Budget      Amount
Admin       250.00
Development 350.00
IT          390.00
Tech        100.00

Table 3: Remaining commitment -> summed by budget, where a commitments exists AND less expenses where expenses have been posted to the commitment.

select
    C.Budget, T.Amount - SUM(E.Amount) as RemainingCommitmentAmount
from
    Commitment C
inner join
    Expense E on C.RefNumber = E.RefNumber
inner join
    (select Budget, SUM(Amount) as Amount from Commitment C group by Budget) T
        on T.Budget = C.Budget
group by
    C.Budget, T.Amount

Output:

Budget  RemainingCommitmentAmount
Admin   20.00
IT      760.00

Edit: Combining them into a single output is as simple as left joining the three results together. Since we want all the budgets to display for sure, we use that as the base query, but since there may or may not be relevant expenses or commitments, we want to join those other results using a left join. Also, we will use the COALESCE command to replace NULL values with 0. Here is what the query would look like:

select
    B.Name as `Budget Item`,
    B.Amount as `Budget amount`,
    COALESCE(E.Amount, 0) as `Expenses`,
    COALESCE(C.RemainingCommitmentAmount, 0) as `Remaining commitment`
from
    (
        select
            Name, Amount
        from
            Budget B
    ) B
left join
    (
        select
            Budget, sum(Amount) As Amount
        from
            Expense E
        group by
            Budget
    ) E on B.Name = E.Budget
left join
    (
        select
            C.Budget, T.Amount - SUM(E.Amount) as RemainingCommitmentAmount
        from
            Commitment C
        inner join
            Expense E on C.RefNumber = E.RefNumber
        inner join
            (select Budget, SUM(Amount) as Amount from Commitment C group by Budget) T
                on T.Budget = C.Budget
        group by
            C.Budget, T.Amount
    ) C on C.Budget = B.Name

Output:

Budget item Budget amount   Expenses    Remaining commitment
IT          5000.00         390.00      760.00
Admin       1000.00         250.00      20.00
Tech        300.00          100.00      0.00
Development 9000.00         350.00      0.00

Hope this helps!