SQL: Records from two views into one 1

[zemp]

I have two views in SQL server that work great in summarizing data from two separate groups of tables in my database.

View1 has fields ID, RInv, RAmount, RDate, RAmtPaid
View2 has fields ID, CInv, CAmount, CDate, CAmtPaid

The correspondinmg fields are of the same data type and are linked by the ID fields.

What I would like is to get all of the records into a new view for a single ID value in the following format,

View3 has fields ID, Invoice, Amount, Date, AmtPaid

For example if View1 has 2 records for ID=10 and View2 has 1 records for ID=10 then view3 should have 3 records for ID=10.

First of all is this possible, secondly if so, how can I get it done.



Take Care,

zemp

"If the grass looks greener... it's probably because there is more manure."

 

[JoeSack]

Hi,

You could use a UNION operation. For example:

SELECT ID, RInv as Invoice, RAmount as Amount, RDate as , RAmtPaid as AmtPaid
FROM View1
UNION
SELECT CInv, CAmount, CDate, CAmtPaid
FROM View2


I added the "as" in order to display the fields in the view as you indicated...

Best Regards,
Joseph Sack,

http://www.JoeSack.com

Author of "SQL Server 2000 Fast Answers for DBAs and Developers".

 

[zemp]

Mnay thanks Joe, sooner or later I will remember the

UNION

keyword. I made some slight adjustments and what I have below works great. Thanks again, a star for you.

SELECT  ID, RInv as Invoice, RAmount as Amount, RDate as InvDate, RAmtPaid as AmtPaid
FROM View1 WHERE ID = 10
UNION 
SELECT ID, CInv, CAmount, CDate, CAmtPaid
FROM View2  WHERE ID = 10

Take Care,

zemp

"If the grass looks greener... it's probably because there is more manure."