SQL Server Question About Query in SQL With 2 tables

2sbarzqh 于 2023-06-21 发布在其他

关注(0)|答案(1)|浏览(146)

I have 2 tables in sqlserver
table1 is A Header Named : Accounts
Table 2 Is Body Named : transaction

Accounts :
| AccID | Accname | AccSubCode |
| ------------ | ------------ | ------------ |
| 1 | Account1 | 3 |
| 2 | Account2 | 3 |
| 3 | Account3 | 1 |

Transaction:

ID	ACCID	Debt	Credit	Post
1	1	500		1
2	1		300	0
3	1		100	0

So I want the result show All Accounts Name Where AccSubcode = 3 And Post = 1
then Column show sum( debt ) - Sum (Credit ) as balance Column
I tried Code But it's Show me AccName if only has a Actions in transaction Table
and i want to Get All Accounts Name where AccSubcode = 3 And Post = 1 whith the balance Column even there is no any Actions For AccID in transaction table like this

AccName	ACCID	balance
Accounts1	1	500
Accounts2	2	0

i tried this Code but show only Accounts name if there is a transaction in transaction table

SELECT 
     A.AccName
    ,Max(ISNULL(A.AccID, 0)) AS [AccID]
    ,SUM(ISNULL(T.Debt, 0)) - SUM(ISNULL(T.Credit, 0))AS [Balance]
       FROM Accounts AS A
       LEFT JOIN Transaction AS T
       ON A.AccID = T.AccID
        where A.AccSubCode = 3 and T.Post = 1
        GROUP BY A.AccID, A.AccName

sql-server

来源：https://stackoverflow.com/questions/76446133/question-about-query-in-sql-with-2-tables

1条答案

按热度按时间

j13ufse21#

To list all accounts even those with post = 0, then join by AccID and T.Post = 1 as follows :

SELECT A.AccName,
       A.AccID,
       SUM(ISNULL(T.Debt, 0)) - SUM(ISNULL(T.Credit, 0)) AS [Balance]
FROM Accounts AS A
LEFT JOIN Transactions AS T ON A.AccID = T.AccID AND T.Post = 1
WHERE A.AccSubCode = 3
GROUP BY A.AccID, A.AccName

Demo here

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server Question About Query in SQL With 2 tables

1条答案

相关问题

热门标签

最新问答