Geek Answers Handbook

Sharing SQL JOIN and UNION

Ok, I'm confused why I get the following behavior.

I have a "transaction header" table and a transaction table. For a particular function, the detail table requires a bit of normalization to retrieve the "Note" data. Each data record can contain up to 3 notes, indicated by columns TranRemark1, TranRemark2 and TranRemark3.

I put together the following query, thinking that it will work, but it will return the wrong 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 resulting result set is based on the number of TranHeader entries that match the clientNN, the number of entries that match the where query of TranDetail. For example, if the client is "Acme Inc." has 3 entries in the header table, and I use the above query for the comment code "1234" (which corresponds to only 1 entry in TranDetail), the correct entries are listed 3 times in the result set.

EDIT Therefore, I would expect from the above example to get this result:

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

What I get is:

 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 TranHeader may have multiple entries for the client.

I tried a direct and complete connection, but it all works out the same way.

Where am I missing the problem?

Thanks for the help.

+4
source share
3 answers

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;
+4
source

What 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 originally 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 need a separate line for each comment?

+4
source

If you are doing UNION ALL, then do SELECT DISTINCT on OUTER SQL. If you execute UNION, it will take care of duplicates.

UNION ALL with DISTINCT gives better performance, although I think ....

0
source

More articles:


All Articles