SQL QUERY ISSUE (NOT IN )

select NAME  
from Temp_EMP 
where name not in  select NAME from  EMPLOYEE  and deptid ='11'

I am getting an error in the statement. 开发者_开发百科

You are missing the WHERE clause in the subquery:

select NAME from Temp_EMP where name not in (select NAME from EMPLOYEE WHERE deptid ='11')

Verrigo is close, but I think it should be more like this:

select [NAME]
from Temp_EMP
where name not in (
    select [Name]
    from EMPLOYEE
) and deptid = '11'

I'm assuming deptid is a column on Temp_EMP, and that deptid is in fact some text field.

Josh

Try

select [NAME] from Temp_EMP where [name] not in (select NAME from EMPLOYEE) and deptid ='11'

Or, i think this is a bit more optimized:

Select [Name] from temp_emp t left join
employee on e on t.[name]=e.[name]
where e.[name] is null and t.deptid = '11'