Merge 2 Tables

Question

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

Rousseau Sennett · Answer

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 Chand · Answer

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.