There are 2 tables Ledger and transaction
Ledger Table has below fields
LID,LName,Lgroup,Opbal
Transaction Table has Txno,Tandate,LID,TXnType,Amount,Narration
Below Are The data
Ledger Table
LID, LName, Lgroup, Opbal DrCr
1 Salary Expense 10000 DR
2 Cash Asset 20000 DR
3 Electricty Expense 1000 DR
Transaction Table has
Txno, Tandate, LID, debit credit
1 2022-01-01 2 10000
1 2022-01-01 1 2000
1 2022-01-01 3 8000
Required Ledger Display
If Cash Ledger is selected
Txno Txndate Lanme Debit Credit Balance
1 2022-01-01 Salary 2000 18000
1 2022-01-01 Electricty 8000 10000
If Salary Ledger is selected
Txno Txndate Lanme Debit Credit Balance
1 2022-01-01 cash 2000 12000
If Electricity Ledger is selected
Txno Txndate Lanme Debit Credit balance
1 2022-01-01 cash 8000 7000
Have tried the below SQL but it does not work - for example if I query Salary ledger the all the ledger including Salary ledger is displayed, rather than the expected result I showed above.
What I have tried:
SELECT T1.TxnNo,
T1.Txndate,
t2.LName,
ISNULL(T1.Debit, 0) AS Dr,
ISNULL(T1.Credit, 0) AS Cr
FROM [Transaction] T1
INNER JOIN (SELECT LID,
LName
FROM Ledger
WHERE LID IN (SELECT ledger.LID FROM Ledger WHERE LID = 3)) t2 ON T1.Lid = t2.LID
INNER JOIN (SELECT TxnNo, Lid FROM [Transaction] WHERE Lid = 3) t3 ON t3.TxnNo = T1.TxnNo ;