Mysql在第三个表上加入三个带WHERE的表

I have three tables I need to join to show the following.
I can join the first two and show all the unique nominal_acc_no's with this query

我有三个表需要加入以显示以下内容。我可以加入前两个并使用此查询显示所有唯一的nominal_acc_no

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;

but I'm struggling with how to join the third and output the results shown below. I need to GROUP ON nominal_acc_no and SUM the debit & credit with a WHERE on the company_id in the acc_posting_details table. Any and all help appreciated.

但我正在努力加入第三个并输出如下所示的结果。我需要在acc_posting_details表中的group_id上对GROUP ON nominal_acc_no和SUM借记和贷记进行汇总。任何和所有帮助赞赏。

 Acc_chart_of_accounts           
Coa_id   nominal_acc_no 
    1           10   
    2           20        

acc_chart_of_sub_accounts
coa_sub_id    nominal_acc_no  company_id
      1             10                 1
      2             20                 1
      3             110                1



Acc_posting_details
Id         nominal_acc_no   debit    credit  company_id
1              10           25.00                1
2              10           15.00                1
3              20                    30.00       1
4              110          10.00                1
5              110           8.00                1

Result
Nominal_acc_no        debit       credit    company_id
10                    40.00         -           1
20                      -         30.00         1
110                   18.00         -           1

3 个解决方案

#1

SELECT
  acsa.nominal_acc_no,
  IFNULL(sum(apd.debit),0) AS debit,
  IFNULL(sum(apd.credit),0) AS credit,
  acsa.company_id
FROM acc_chart_of_sub_accounts AS acsa
  LEFT JOIN acc_posting_details AS apd
    ON acsa.nominal_acc_no = apd.Nominal_acc_no
GROUP BY acsa.nominal_acc_no, acsa.company_id

Output

Nominal_acc_no        debit     credit    company_id
-----------------------------------------------------
10                    40.00     0           1
20                    0         30.00       1
110                   18.00     0           1   

EDIT : There was an alias problem i have fixed in my query.

编辑:我的查询中修复了别名问题。

SQL Fiddle Demo Here

#2

SELECT Nominal_acc_no,SUM(debit) AS total_debit,SUM(credit) AS total_credit from   Acc_posting_details group by nominal_acc_no;

You can insert other where conditions before group by.

您可以在group by之前插入其他条件。

#3

Can you show the complete structure of your tables, that is, all the columns?

你能展示你的表的完整结构,即所有列吗?

Your first SELECT statement refers to a column coa_name within tables acc_chart_of_accounts and acc_chart_of_sub_accounts, but then you don't mention that column in description of those tables. That makes me think there might be other relevant information that is missing.

您的第一个SELECT语句引用表acc_chart_of_accounts和acc_chart_of_sub_accounts中的列coa_name,但是您没有在这些表的描述中提及该列。这让我觉得可能还有其他相关信息缺失。

Do tables acc_chart_of_accounts and acc_chart_of_sub_accounts contain any debits or credits? If not, why do you need info from them? Based on what I understand from your question, it seems that Acc_posting_details has all the info you need:

表acc_chart_of_accounts和acc_chart_of_sub_accounts是否包含任何借方或贷方?如果没有,为什么你需要他们的信息?根据我对您的问题的理解,似乎Acc_posting_details具有您需要的所有信息:

"I need to GROUP ON nominal_acc_no and SUM the debit & credit with a WHERE on the company_id in the acc_posting_details table"

“我需要GROUP_ nominal_acc_no并使用acc_posting_details表中company_id上的WHERE借记和贷记余额”

Does this query solve your problem?

这个查询能解决您的问题吗?

 SELECT Nominal_acc_no,
        SUM(debit) AS total_debit,
        SUM(credit) AS total_credit,
        company_id
 FROM Acc_posting_details
 WHERE company_id = 1
 GROUP BY nominal_acc_no;

ADDED:

If I understand you last comment, you need to get exctly one row for every Nominal_acc_no that exists in acc_chart_of_accounts.

如果我理解你最后的评论,你需要为acc_chart_of_accounts中存在的每个Nominal_acc_no提供一行。

In that case, Acc_posting_details does not have all the info you need, because some nominal accounts might have no debits or credits, and so they don't exist in Acc_posting_details.

在这种情况下,Acc_posting_details没有您需要的所有信息,因为某些名义账户可能没有借方或贷方,因此它们在Acc_posting_details中不存在。

 SELECT aca.Nominal_acc_no,
        IFNULL(SUM(apd.debit),0) AS total_debit,
        IFNULL(SUM(apd.credit),0) AS total_credit,
        aca.company_id
 FROM acc_chart_of_accounts aca
 LEFT OUTER JOIN Acc_posting_details apd
              ON aca.Nominal_acc_no = apd.Nominal_acc_no
 WHERE aca.company_id = 1
 GROUP BY aca.nominal_acc_no;

This query will give you one row for every nominal account in acc_chart_of_accounts. If an account has any rows in Acc_posting_details, you will get their credits and debits added together. For accounts that have no records in Acc_posting_details, you will get 0 in the total_debit and total_credit columns.

此查询将为acc_chart_of_accounts中的每个名义帐户提供一行。如果帐户在Acc_posting_details中有任何行,您将获得他们的信用和借记。对于Acc_posting_details中没有记录的帐户,您将在total_debit和total_credit列中获得0。

You said that you want company_id in WHERE clasue. Alternatively, if you want a complete report over all the companies, you would remove the WHERE clause and replace the GROUP BY clause with

你说你想在WHERE clasue中使用company_id。或者,如果您想要对所有公司进行完整报告,则应删除WHERE子句并将GROUP BY子句替换为

 GROUP BY aca.nominal_acc_no, aca.company_id;

Is this what you were looking for? Let me know if it works.

这是你在寻找什么?如果有效,请告诉我。