We help IT Professionals succeed at work.

how can i merag beetween 2 tables in sql ?

Tech_Men
Tech_Men asked
on
Medium Priority
206 Views
Last Modified: 2012-06-27
hi there , i have a Financial software in the Db i have 2 tables

invoice table
receipt  table

i have a cust that have 2 invoice and 1 receipt
i want to get the balance

i need to get all the invoice  sum and get all the receipt sum as well to calculate the his balance

but i need to see the actions like in the bank
invoice    balance
-500        -500
-500       -1000
500(receipt) so his balance is  -500
i am on c# dot net 1.0 WinForms App.
thanks ....


       

Comment
Watch Question

Commented:
Create a view that will calculate the sum of the invoice and of the receipt and the made a relation between them

e.g
let's say that we have this tables:

Invoice table        
ID
ID_CUSTOMER
INV_VAL

and

Receipt table  
ID      
ID_CUSTOMER
REC_VAL

The view should look like something like this:

SELECT ID_CUSTOMER, SUM(INVOICE) as INVOICE, SUM(RECEIPT) as RECEIPT, SUM(INVOICE)-  SUM(RECEIPT) as BALANCE
FROM

(SELECT        ID_CUSTOMER, SUM(INV_VAL) AS INVOICE, 0 AS RECEIPT
FROM            INVOICE
GROUP BY ID_CUSTOMER
UNION
SELECT        ID_CUSTOMER, 0 AS INVOICE, SUM(REC_VAL) AS RECEIPT
FROM            RECEIPT
GROUP BY ID_CUSTOMER) BAL_VIEW

GROUP BY ID_CUSTOMER


The view will show ID_CUSTOMER, INVOICE,RECEIPT and BALANCE.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.