I created a query in SQL Server (2005) that includes a UNION clause. If I run each of the queries separately (without a UNION) the provide the expected results. When I run the query with the UNION clause the query executes (without errors or warnings) I get results EXCEPT that I 'lose' records from the generated results set.
In writing the query I paid particular attention to field data types to make sure they matched. In the second query there is no Inner Join on the two tables - this was done be design so as not to lose certain records.
Any thoughts/consideration when doing this type of query?
Here is the syntax:
SELECT MatterID, MatterInfoMatterNum, MatterInfoSortName, ClientInfoClientID, TBankAllocInfoStatus, TBankAllocInfoCheckID, TBankAllocInfInvDate, TBankAllocInfoAmount, TBankAllocInfoEntryType, TBankAllocInfActivityID, TBankAllocInfExplanation, TBankCommInfStatus, TBankCommInfDate, TBankCommInfEntryType, TBankCommInfAmount, TBankCommInfPaidTo, ActivityCodesNickname, ActivityCodesName
FROM ((MattInf MattInf INNER JOIN TBAlloc TBAlloc ON MattInf.MatterID=TBAlloc.MatterID) INNER JOIN ActCode ActCode ON TBAlloc.TBankAllocInfActivityID=ActCode.ActivityCodesID) INNER JOIN TBComm TBComm ON TBAlloc.TBankAllocInfoCheckID=TBComm.TBankCommInfSequenceID
SELECT MatterID, MatterInfoMatterNum, MatterInfoSortName, ClientInfoClientID,
0 as TBankAllocInfoStatus, null as TBankAllocInfoCheckID, 0 asTBankAllocInfInvDate,
0 as TBankAllocInfoAmount, 0 as TBankAllocInfoEntryType, 0 as TBankAllocInfActivityID,
‘ ‘ as TBankAllocInfExplanation, 0 as TBankCommInfStatus, 0 as TBankCommInfDate,
0 as TBankCommInfEntryType, 0 as TBankCommInfAmount, ‘ ‘ as TBankCommInfPaidTo,
FROM MattInf, ActCode
Thanks for your input.