UNION of data question

[WB786]

I am new to SQL queries and need to build one that involves 3 tables. tblClients, tblPayments, tblReceipts. tblPayments and tblReceipts have the foreign key ClientID.

This is what I have so far, which ofcourse doesn't work:

SELECT Clients.SSN, Clients.LastName, Clients.FirstName, Payments.Type, Payments.Amount, Payments.Created
FROM CLIENTS INNER JOIN Payments ON Clients.ClientID = Payments.ClientID
WHERE Payments.Created between '2001-08-08' and '2001-12-31' and Payments.Type in(1,2,4)
UNION
SELECT Receipts.ReceiptAmount, Receipts.DateReceived
FROM Receipts

I am getting an error that says to convert money to nvarchar.

I read a little bit about UNION queries but can't seem to figure it out.

Thanks in advance,


WB

 

[dan1967]

The thing about UNION queries is that all queries being UNIONed need to have the same output columns with the same data types. For example, if query1 has Name, Age, Salary query 2 needs to have Name, Age, Salary.

Dan.

 

[WB786]

What I want to do is be able to drop the ReciptAmount and Recipts date in the Amount and Created column respectively. All of the three tables don't have the same name columns so then how can I do this then.

Thanks,


WB

 

[WB786]

Ok figured it out. Here is what it finally looks like:

SELECT Right(SSN,4)SSN, Left(LastName,4)LastName,FirstName, Amount, Type
FROM CLIENTS INNER JOIN Payments ON Clients.ClientID = Payments.ClientID
WHERE Created between '2001-08-08' and '2001-12-31' and Type in(1,2,4) and SSN IS NOT NULL
UNION
SELECT Right(SSN,4)SSN, Left(LastName,4)LastName,FirstName, ReceiptAmount, ReceiptID
FROM CLIENTS INNER JOIN Receipts ON Clients.ClientID = Receipts.ClientID
WHERE DateReceived between '2001-08-08' and '2001-12-31' and SSN IS NOT NULL
ORDER BY Clients.SSN ASC

Thanks.

WB