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.
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" |
|
|
rohitkumar
Constraint Violating Yak Guru
472 Posts |
Posted - 2008-07-22 : 09:18:59
|
SELECT CUSTOMER_ID, COUNT(CUSTOMER_ID) AS COUNT FROM TABLE_AWHERE TABLE_A.CUSTOMER_ID IN (SELECT DISTINCT CUSTOMER_ID FROM TABLE_B)GROUP BY TABLE_A.CUSTOMER_ID |
|
|
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" |
|
|
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)' |
|
|
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 thisfrom JB53000T A, JB53100T B where B.EKUNDEID = A.EKUNDEIDYou have duplicate kundeid in on (or both) of those tables. E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|