Click here to Skip to main content
15,881,882 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I Hi, Good Morning everyone.

I have a doubt for calculating debit and credit.
I have two table
Table 1 (Voucher): VoucherNo,VoucherName,VoucherDate, Debit.
Table 2 (Receipt):ReceiptNo,ReceiptName,Credit.

I need out put like below
VoucherNo  VoucherName Debit  Credit
001         Purcharse  1000      0
02           sales       0       2500

and so on

This is my query
SQL
SELECT VoucherNo,VoucherName,Debit, NULL as Credit
FROM tbl_Voucher
UNION ALL
SELECT ReceiptNo,ReceiptName,Credit,NULL as Debit
FROM tbl_Receipt

but i don't get proper output

Help me
Posted
Updated 28-Dec-15 10:47am
v3
Comments
Mehdi Gholam 28-Dec-15 3:47am    
... and what is the "correct" output?
[no name] 28-Dec-15 4:04am    
Provide data with your tables. Secondly is there any relation between two tables.
Balakrishna Kuppam 28-Dec-15 5:34am    
Hi, Manas_Kumar
in both table id is common column
Sinisa Hajnal 28-Dec-15 5:26am    
You should use LEFT JOIN or INNER JOIN to join the tables. You'll have to have a field in common with two tables through which you can connect particular receipt with the voucher.

1 solution

A join would be better assuming that one table is the "main" table. For example, if all records will exist in tbl_Receipt then do something like this:

For example:
SQL
SELECT r.VoucherNo, r.Credit, v.Debit
FROM tbl_Receipt r
LEFT JOIN tbl_Voucher v ON r.ID = v.ID


Or, you could wrap your existing SQL into a SELECT statement:
SQL
SELECT x.VoucerhNo, x.VoucherName, SUM(x.Debit), SUM(x.Credit)
FROM (
  SELECT VoucherNo,VoucherName,Debit, NULL as Credit
  FROM tbl_Voucher
  UNION ALL
  SELECT ReceiptNo,ReceiptName,Credit,NULL as Debit
  FROM tbl_Receipt
) x 
GROUP BY x.VoucherNo, x.VoucherName


The join will likely be a better approach.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900