Matching Records (Gifts that exist in both the Batch and Gift table)

select BG.UserGiftId, GF.UserGiftId, GF.GiftSubType, GF.ID, * 
from BATCHGIFT BG
inner join GIFT GF on GF.USERGIFTID = BG.USERGIFTID
left outer join BATCHHEADER BH on BG.BatchHeaderID = BH.BATCHID
where BH.BATCHNUMBER = '40' -- specific batch number 


Non-Matching Records (Gifts that exist in the Batch but not in the Gift table)

select BH.BatchNumber, BG.UserGiftId, BG.Amount, BG.ConstitName, BG.Constit_Id, BG.Constituent_Code,
CASE BG.Gift_Status
    WHEN 1 THEN 'Active'
    WHEN 2 THEN 'Held'
    WHEN 3 THEN 'Terminated'
    WHEN 4 THEN 'Completed'
    WHEN 5 THEN 'Cancelled'
    ELSE 'Unknown' 
END as 'Gift Status', convert(varchar, BG.Dte, 101) as 'Gift Date', convert(varchar, BG.Post_Date, 101) as 'Post Date', 
CASE BG.Post_Status
    WHEN 1 THEN 'Posted'
    WHEN 2 THEN 'Not Posted'
    WHEN 3 THEN 'Do No Post'
    ELSE 'Unknown'
END AS 'Post Status' 
    
from BatchGift BG
inner join BATCHHEADER BH on BG.BatchHeaderID = BH.BATCHID

where BG.UserGiftId is not NULL and
BG.UserGiftId NOT IN
    (SELECT GF.UserGiftId 
     FROM GIFT gf where gf.UserGiftId is not NULL) 

Disclaimer: We provide programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes you are familiar with Structured Query Language and the tools used to create and modify SQL statements and Crystal Reports. Our Customer Support may help explain the functionality of a particular procedure, but we will not modify, or assist you with modifying, these examples to provide additional functionality.