0

I work at a school and we are trying to run a query to see all students that should be starting in the next week who don't already have an AD account (we use ID numbers in the pager field and so search on that). We have linked SQL to Ad so we can run these queries and never had any issue but when I run the following I don't get any results.

select *
from FutureStudents
where id not in (select * from OPENQUERY(ADSI, '
    SELECT pager
    FROM ''LDAP://OU=students,DC=stcatherines,DC=net,DC=au'' 
    WHERE objectCategory = ''Person'' 
    AND objectClass = ''user'' '
    )
)

When I run the not in command I expect that I should get one result but I do not. But if I change the query to in (instead of not in) I do get all the students that are in the table who have accounts. Plus at the same time as running these queries I have done a simple query looking for the pager in AD that I have been moving in order to get a result and when the account is in the student OU I get a result and when it is moved I do not.

So I am fairly sure is something to do with the not in statement. Am I using the right statement or is there something different I should be doing to get true results?

anton.burger
  • 5,637
  • 32
  • 48
user1522721
  • 1
  • 1
    Similar question here: http://stackoverflow.com/questions/129077/sql-not-in-constraint-and-null-values Does that help? – Mr Lister Jul 13 '12 at 06:43
  • 1
    Use `NOT EXISTS` rather than `NOT IN`, it eliminates problems with `NULL` values. – GarethD Jul 13 '12 at 07:46
  • Or just add `AND pager IS NOT NULL` to your `OPENQUERY` – Martin Smith Jul 13 '12 at 09:11
  • 2
    If using NOT IN and null values are eliminated, it still can't be NOT IN (SELECT * ... That needs to be NOT IN (SELECT some_column – Aaron Bertrand Jul 13 '12 at 12:28
  • @AaronBertrand: Are you sure? [The Transact-SQL documentation](http://msdn.microsoft.com/en-us/library/ms177682.aspx) stipulates that the subquery return only one column, but it doesn't mention any problem with `SELECT *`. (Granted, `SELECT *` is not the most intuitive way to return one column, but it's not really a problem here.) – ruakh Jul 13 '12 at 14:07
  • 2
    @ruakh When `SELECT *` happens to map to a single column, you can "get away with it" - that doesn't make it good practice. Why use `*` when you could explicitly name the column you're after? Then the outer query doesn't break later when underlying query/structure changes. I don't see any advantage to using `*` here instead of being explicit, except to save a few keystrokes. – Aaron Bertrand Jul 13 '12 at 14:09
  • 1
    @AaronBertrand: Firstly -- you used language like "can't be" and "needs to be", which is wrong, and you didn't explain *why* you prefer an explicit column-name, which was unhelpful. (Subjective preferences should always be explained, IMHO.) Secondly -- I'm not convinced of your presupposition that the OP "could explicitly name the column [(s)he's] after". I've never used `OPENQUERY`, so I can't say for sure, but I'm betting that the names of returned columns aren't available for reference by the containing query. – ruakh Jul 13 '12 at 14:19
  • @ruakh ok I'm sorry you didn't like my language. Don't know how to fix that now. *shrug* My point remains. And yes, you can reference specific column names in `OPENQUERY`... did you try it? – Aaron Bertrand Jul 13 '12 at 14:22

0 Answers0