Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 wrong result from count(*)...

Author  Topic 

WhatSQL
Starting Member

2 Posts

Posted - 2008-07-22 : 09:05:39
Hi all!
Im having a bit of a problem. I have to searvh a table to find how many rows who haves the same customerid(kunde_nr) as the customerid from a different table. So if my customer not exist in BOTH tables I do not want the customer in my result set. All the customers the result must have their customerid in both tables. Okay?
Heres how i have done it:
set @sql = @sql + ' and(select count(*) from CO55800T where KUNDE_NR collate SQL_Danish_Pref_CP1_CI_AS = A.kundenr) > 0'

If it counts more than 0 then the customer is valid.
The acctual sql statement is much bigger but that is the part making the trouble.
If I run this part alone in a query it returns the right result, but when it is used in the complete statement it returns wrong result. It returns +2 customers whom only exist in the A table and not in the CO55800T table as it must to be a part of the result.
Can anyone see what is wrong or where the problem might be?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-22 : 09:17:33
This is because of the JOIN predicate ( WHERE KUNDE_NR = a.kundenr).
Try with EXISTS instead.
set @sql = @sql + ' and exists (select * from CO55800T where KUNDE_NR collate SQL_Danish_Pref_CP1_CI_AS = A.kundenr)'



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-22 : 09:18:59
SELECT CUSTOMER_ID, COUNT(CUSTOMER_ID) AS COUNT FROM TABLE_A
WHERE TABLE_A.CUSTOMER_ID IN (SELECT DISTINCT CUSTOMER_ID FROM TABLE_B)
GROUP BY TABLE_A.CUSTOMER_ID
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 03:30:16
I saw on the other site that the problem still exists.
quote:
select A.EKUNDEID,
RTRIM(A.KUNDENR) as kundenr,
RTRIM(B.ANAVNB) as navn,
RTRIM(B.ABYB) as postnummerby,
RTRIM(A.AEMAIL) as email,
A.SAPKUNDENR,
B.AKONTAKT
from JB53000T A, JB53100T B
where B.EKUNDEID = A.EKUNDEID'

if(@kundenr <> '')
set @sql = @sql + ' and A.kundenr like ''' + @kundenr + ''''
...
...
other statements to complete the search for the customers
...
set @sql = @sql + ' and exists(select * from CO55800T where KUNDE_NR collate SQL_Danish_Pref_CP1_CI_AS = A.kundenr)'
...

exec(@sql)

The problem is that you hva duplicate records for some kundenr in either JB53000T table or JB53100T table.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

WhatSQL
Starting Member

2 Posts

Posted - 2008-07-23 : 03:39:39
Hi again... Sorry for misleading you but the result of the the statement contains all the customers. I have two almost identical statements and one of them returns all the customers and the other returns 4 correct customers and two false.

Returns all the customers:
set @sql = @sql + ' and exists(select * from CO55800T where KUNDE_NR collate SQL_Danish_Pref_CP1_CI_AS = A.kundenr)'

Returns 4 right and 2 wrong:
set @sql = @sql + ' and exists(select * from JB53400T where fk_kundenr collate SQL_Danish_Pref_CP1_CI_AS = A.kundenr)'
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 04:13:56
The problem is not the EXISTS part.

The part that yields the duplicate records is this

from JB53000T A, JB53100T B
where B.EKUNDEID = A.EKUNDEID

You have duplicate kundeid in on (or both) of those tables.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
   

- Advertisement -