Using SQL JOIN and UNION together

Question

OK, I stumped as to why I'm getting the following behavior.

I've got a "transaction header" table and "transaction detail" table. For a certain function, the detail table requires a bit of normalization to extract "Remark" data. Each detail record can have up to 3 remarks in it designated by the TranRemark1, TranRemark2 and TranRemark3 columns.

I put together the following query thinking it would work, but it returns the incorrect number of records.

SELECT  
b.ClientName,
a.TranID,
a.TranRemark1,
a.TranDateOfService,
a.TranPayment   
FROM
(select TranRemark1, TranID from TranDetail
union all
select TranRemark2, TranID from TranDetail
union all
select TranRemark3, TranID from TranDetail) AS a
LEFT JOIN TranHeader AS b ON
b.TranID = a.TranID
WHERE a.TranRemark1 = @RemarkCode;

The result set I get is based on the number of TranHeader records that match the ClientName NOT the number of records that match the where clause from TranDetail. For example, if Client "Acme Inc." has 3 records in the header table and I use the above query for remark code "1234" (which matches only 1 record in TranDetail) the result set lists the correct record 3 times.

EDIT So I'd expect from the above example to get a result set like this:

ClientName--TranID--TranRemark1--TranDateOfService--TranPayment
Acme Inc    ADC11   1234         8-16-2011          45.11    

What I get is this:

ClientName--TranID--TranRemark1--TranDateOfService--TranPayment
Acme Inc    ADC11   1234         8-16-2011          45.11    
Acme Inc    ADC11   1234         8-16-2011          45.11  
Acme Inc    ADC11   1234         8-16-2011          45.11  

Keep in mind that there can be multiple records for a client in TranHeader.

I've tried right and full join, but it all comes out the same.

Where am I missing the problem?

Thanks for the help.

Answer 1

Can you try replacing:

LEFT JOIN TranHeader AS b ON
b.TranID = a.TranID
WHERE a.TranRemark1 = @RemarkCode;

with:

LEFT JOIN
  ( SELECT DISTINCT
        TranId, ClientName
    FROM TranHeader
  ) AS b ON
b.TranID = a.TranID
WHERE a.TranRemark1 = @RemarkCode;