Using SQL JOIN and UNION together

Can you try replacing:

LEFT JOIN TranHeader AS b ONb.TranID = a.TranIDWHERE a.TranRemark1 = @RemarkCode;

with:

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

How about

SELECT  b.ClientName,  a.TranID,  a.TranRemark1,  a.TranDateOfService,  a.TranPaymentWHERE a.TranRemark1 = @RemarkID JOIN TranHeader b ON b.TranID = a.TranIDUNION ALLSELECT  b.ClientName,  a.TranID  a.TranRemark2,  a.TranDateOfService,  a.TranPaymentWHERE a.TranRemark2 = @RemarkID JOIN TranHeader b ON b.TranID = a.TranIDUNION ALLSELECT  b.ClientName,  a.TranID,  a.TranRemark3,  a.TranDateOfService,  a.TranPaymentWHERE 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   FROMTranDetail a JOIN TranHeader As b ONb.TranID = a.TranIDWHERE a.TranRemark1 = @RemarkCode   OR a.TranRemark2 = @RemarkCode   OR a.TranRemark3 = @RemarkCode;

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

If doing a UNION ALL, then do a SELECT DISTINCT on the OUTER SQL. If doing a UNION, it will take care of the duplicates.

The UNION ALL with a DISTINCT gives better performance though I believe....