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.

Solution 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;

Solution 2

How about

SELECT
  b.ClientName,
  a.TranID,
  a.TranRemark1,
  a.TranDateOfService,
  a.TranPayment
WHERE a.TranRemark1 = @RemarkID JOIN TranHeader b ON b.TranID = a.TranID
UNION ALL
SELECT
  b.ClientName,
  a.TranID
  a.TranRemark2,
  a.TranDateOfService,
  a.TranPayment
WHERE a.TranRemark2 = @RemarkID JOIN TranHeader b ON b.TranID = a.TranID
UNION ALL
SELECT
  b.ClientName,
  a.TranID,
  a.TranRemark3,
  a.TranDateOfService,
  a.TranPayment
WHERE a.TranRemark3 = @RemarkID JOIN TranHeader b ON b.TranID = a.TranID

?

I initially suggested

SELECT  
b.ClientName,
a.TranID,
a.TranRemark1,
a.TranDateOfService,
a.TranPayment,
a.TranRemark1,
a.TranRemark2,
a.TranRemark3   
FROM
TranDetail a JOIN TranHeader As b ON
b.TranID = a.TranID
WHERE a.TranRemark1 = @RemarkCode 
  OR a.TranRemark2 = @RemarkCode 
  OR a.TranRemark3 = @RemarkCode;

but thought you probably want a separate line for each remark?

Solution 3

use distinct while fetching data

SELECT  distinct
b.ClientName,
a.TranID,
a.TranRemark1,
a.TranDateOfService,
a.TranPayment   
FROM`enter code here`
(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;

Comments

[nth]

Nathan - I tried your first option and got the same result as my initial query - detail records multiplied by the number of header records. RE the second option, yes I wanted the result set to be "normalized" i.e. have a separate line for each remark.

[nth]

Yep, Adding DISTINCT appears to take care of the issue. Thanks. The following will also work: SELECT DISTINCT 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;

[David Swindells]

Hi Peeyush, welcome to SO. Great answer, to improve it, maybe you could include a little more detail as to why your answer is correct and to help the author of the original question?