Merge 2 Tables

Rousseau Sennett

15y2.7kSQL

I'm sure the answer to this is obvious but I'm a newbi so here it goes...
I'm working in vs 2010 C# and i'm trying to create a Table adapter in my Data set to do the following

Ok I've got 2 tables
Invoices         Credits
======         =======
Invoice#        ID(int, auto increment)
Date             Date
Debitor          Debitor
X                 X
X                 X
Total            Amount

It would be easy to just draw up the 2 tables between dates nd compate balances. but i would like statement to sort it according to the date for
a single Debitor. to look somthing like this

Date                DR                CR
=====             ================
2011/05/01       150.00
                                         100.00

When I use joins in my SQL Select Query it end up looking like this

2011/05/01      150.00           100.00

Is there a more advance Query I should use so every entry from each table creates its own row when creating my table adapter ?
Or is there a totaly different angle I should be ding this from ? O and I'm using windows Reporting.

Any help would be appreciated

Thx Rousseau

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rousseau Sennett15y ago
I found the solution it was probably my lack of Knowledge about the UNION and I didn't realise you can have NULLS because union requires both selects to be the same and in the same order anyway my simple query looks like this

SELECT        Date, Total AS Debit, NULL AS Credit, InvoiceNum
FROM            Invoice
UNION ALL
SELECT        Date, NULL AS Expr1, Amount, NULL AS Expr2
FROM            Credits

Just replaced with NULLS since both tables didn't have the same fields
Mahesh Chand15y ago
SQL query solution is recommended. But if you can't do in SQL,

you could create a DataTable with the columns you want and then get wanted rows from your other two databases using ImportRow.

Just a thought.