Join three mysql tables with sum & group

Question

I have the following three tables

1. acc_chart_of_accounts
2. acc_chart_of_sub_accounts
3. acc_posting_details

The table acc_chart_of_accounts is used by all users as a starter list of nominal accounts, the table chart_of_sub_accounts contains any new nominal accounts the user adds.The table acc_posting_details contains account transaction entries. The goal is to display a nominal accounts list using the acc_chart_of_accounts table as a base and adding any other nominal codes the user creates to it with the sum for each nominal code,all filtered by a company_id. To do this I have tried the following:

I have taken the acc_chart_of_accounts table and the acc_chart_of_sub_accounts table and applied the following query to the two tables below..

acc_chart_of_accounts

nominal_acc_no coa_name

---

        10  Freehold property cost             
        20  Freehold property depreciation     
       110  Leasehold property cost            
       120  Leasehold property depreciation    
       210  Plant and machinery cost           

acc_chart_of_sub_accounts

nominal_acc_no coa_name

---

      1200  Bank current account  

This is the query to list the nominal accounts from the main table acc_chart_of_accounts and the sub table acc_chart_of_sub_accounts set up by the user...

SELECT nominal_acc_no,coa_name FROM
(
    SELECT nominal_acc_no ,coa_name FROM acc_chart_of_accounts
    UNION
    SELECT nominal_acc_no,coa_name FROM acc_chart_of_sub_accounts
) A;

And this is the result it gives, I've removed some of the results to make things easier to read, you can see that the query takes the nominal code entry on the right table acc_chart_of_sub_accounts and inserts it in to the list on the left table acc_chart_of_accounts albeit at the end of the list, I suppose an ORDER BY will sort that.

So when a user adds a new nominal account to their account, it shows up on the main chart as below.

nominal_acc_no coa_name

---

        10  Freehold property cost             
        20  Freehold property depreciation     
       110  Leasehold property cost            
       120  Leasehold property depreciation    
       210  Plant and machinery cost                       
       **1200  Bank current account**    

What I need to do now, is to add the following table acc_posting_details in to the mix, I need to be able to link this table to the other two so that it will add any summed balances to the returned result.

post_detail_id nominal_acc_no debit credit company_id

---

         1            2109    0.00   27.00             0
         2            2200    4.00    0.00             0
         3            7000   23.00    0.00             0
         4            2109    0.00   27.00             0
         5            2200    4.00    0.00             0
         6            6100   23.00    0.00             0

Now when a user looks at their chart of accounts they will see all the nominal codes from the master table plus any other they may have set up as well as any balances which may exist for each nominal code.This query will have to be filtered by company_id which will come from a $_SESSION value.

Below is a result from a query which I managed to produce using the following code:

SELECT
    `acc_chart_of_accounts`.`nominal_acc_no`
    , `acc_chart_of_accounts`.`coa_name`
    , SUM(`acc_posting_details`.`debit`) AS `dabit`
    , SUM(`acc_posting_details`.`credit`) AS `credit`
    , `acc_posting_details`.`company_id`
FROM
    `acc_chart_of_accounts`
    LEFT JOIN `acc_posting_details` 
        ON (`acc_chart_of_accounts`.`nominal_acc_no` = `acc_posting_details`.`nominal_acc_no`)
GROUP BY `acc_chart_of_accounts`.`nominal_acc_no`;

nominal_acc_no coa_name debit credit

---

        10  Freehold property cost             (NULL)  (NULL)        
        20  Freehold property depreciation     (NULL)  (NULL)        
       110  Leasehold property cost            (NULL)  (NULL)        
       120  Leasehold property depreciation    (NULL)  (NULL)        
       210  Plant and machinery cost           (NULL)  (NULL)       
       310  Fixture and fittings cost          (NULL)  (NULL)        
       320  Fixture and fittings depreciation  (NULL)  (NULL)       
       410  Motor vehicles cost                (NULL)  (NULL)        
       420  Motor vehicles depreciation        (NULL)  (NULL)       
       700  Investments                        (NULL)  (NULL)        
       900  Goodwill                           (NULL)  (NULL)        
      1000  Stock                              (NULL)  (NULL)        
      1100  Trade debtors                      (NULL)  (NULL)     
      1103  Debtors and prepayments            (NULL)  (NULL)        
      2100  Purchase ledger control            (NULL)  (NULL)        
      2109  Creditors                            0.00   54.00           
      2200  VAT control acc                      8.00    0.00            
      2300  PAYE NI Liability                  (NULL)  (NULL)        
      2600  Bank loan                          (NULL)  (NULL)        
      2700  Hire purchase                      (NULL)  (NULL)       
      2800  Lease purchase                     (NULL)  (NULL)        
      2900  Other loans                        (NULL)  (NULL)        
      3000  Capital Acc BBF                    (NULL)  (NULL)        
      3100  Capital introduced                 (NULL)  (NULL)       
      3200  Profit and loss account            (NULL)  (NULL)        
      3300  Personal drawings                  (NULL)  (NULL)       
      4000  Sales                              (NULL)  (NULL)        
      4009  Discounts allowed                  (NULL)  (NULL)        
      4100  Eport sales                        (NULL)  (NULL)        
      4200  Royalties received                 (NULL)  (NULL)       
      4210  Commisions received                (NULL)  (NULL)        
      4220  Insurance payout received          (NULL)  (NULL)       
      4230  Rental income                      (NULL)  (NULL)        
      4240  Bank interest received             (NULL)  (NULL)        
      5000  Purchases                          (NULL)  (NULL)       
      5900  Opening stock                      (NULL)  (NULL)       
      5950  Closing stock                      (NULL)  (NULL)        
      6000  Direct labour                      (NULL)  (NULL)        
      6100  Goods out cost                      23.00    0.00           
      6200  Goods in cost                      (NULL)  (NULL)        

this just reads the two account tables, and it doesn't read in the extra nominal account in the acc_chart_of_sub_accounts but it gives the jist of what I want to end up with.I'm really stumped with this, so I could really do with a helping hand. Thanks

David

Answer 1

I think maybe you are looking for a sub select. Your question is kind of rambling, so I'm not sure.

SELECT
    A.`nominal_acc_no`
    , A.`coa_name`
    , SUM(`acc_posting_details`.`debit`) AS `dabit`
    , SUM(`acc_posting_details`.`credit`) AS `credit`
    , `acc_posting_details`.`company_id`
FROM
(
    SELECT nominal_acc_no ,coa_name FROM acc_chart_of_accounts
    UNION
    SELECT nominal_acc_no,coa_name FROM acc_chart_of_sub_accounts
) A
    LEFT JOIN `acc_posting_details` 
        ON (A.`nominal_acc_no` = `acc_posting_details`.`nominal_acc_no`)
GROUP BY A.`nominal_acc_no`;