The best place to ask programming/development questions, imo. UPDATE: stackoverflow is the *worst* place to *ask* questions (if your first question/comment doesn't get any up-rating/response, then u can't ask anymore questions--ridiculously unrealistic), but a great reference for finding answers.

My Music (Nickleus)

20130115

sql getting multiple/duplicate rows for query using IN

in our java jee EJB3 application we have a list page that lists transport agreements domestic records (TADs).
at the top of the table in the header we have an input field where we can filter the results for e.g. postal/zip code (postnummer) for despatch parties (hentested).

the normal, unfiltered list:


there are only 11 TAD records, but when i filtered the search for postal code it looked like this, with duplicated records/rows and the count was 16 (should only be 10 since one of the records doesn't match the filter "00"):



the ql codes looked like this:

for the count value at the bottom (R:16), ("R" means "Records"):

select count(tad.id) from TADS tad , IN (tad.despatchParties) desps WHERE tad.agreementOwnerId = :agreementOwneId  AND desps.postCode LIKE :despatchPostCode

and this, for the actual records in the list:

select tad from TADS tad , IN (tad.despatchParties) desps WHERE tad.agreementOwnerId = :agreementOwneId  AND desps.postCode LIKE :despatchPostCode


the fix for this was actually pretty simple:

for the count value at the bottom:

select count(distinct tad.id) from TADS tad , IN (tad.despatchParties) desps WHERE tad.agreementOwnerId = :agreementOwneId  AND desps.postCode LIKE :despatchPostCode

and this, for the actual records in the list:

select distinct tad from TADS tad , IN (tad.despatchParties) desps WHERE tad.agreementOwnerId = :agreementOwneId  AND desps.postCode LIKE :despatchPostCode

so now the filtered list looks correct:


i see now that this subject is also discussed on stackoverflow:
SQL Server query - Selecting COUNT(*) with DISTINCT

No comments:

Post a Comment