?
Solved

sql Not  in  Query not working

Posted on 2007-10-05
5
Medium Priority
?
142 Views
Last Modified: 2010-05-18
I have the following query

select lower(rtrim(SAMAccountName)) as userID
FROM OPENQUERY( ADSI,'SELECT SAMAccountName FROM ''LDAP://...''
WHERE objectCategory = ''Person'' AND objectClass = ''user'' order by
name')  where SAMAccountName not  in (Select rtrim(UserID) as SAMAccountName from employee)


When I run the select from active directory I get 800 distinct records when I run it on employee I get 766 but when I run this one I get zero back. I want to insert records in active directory to the employeee table if they dont already exist.
0
Comment
Question by:JonMny
  • 2
4 Comments
 
LVL 27

Expert Comment

by:ptjcb
ID: 20025300
select lower(rtrim(SAMAccountName)) as userID
FROM OPENQUERY( ADSI,'SELECT SAMAccountName FROM ''LDAP://...''
WHERE objectCategory = ''Person'' AND objectClass = ''user'' order by
name')  
where lower(rtrim(SAMAccountName)) not  in (Select rtrim(UserID) as SAMAccountName from employee)
0
 
LVL 9

Author Comment

by:JonMny
ID: 20025485
that gives me the same result. zero records
0
 
LVL 9

Accepted Solution

by:
JonMny earned 0 total points
ID: 20030071
I figured it out. It  was because  Select rtrim(UserID) as SAMAccountName from employee was returning null values for some records
0
 
LVL 1

Expert Comment

by:Vee_Mod
ID: 20555647
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question