how to compare with T-SQL

Hello,
I have a list of employees (+5000) that I use to compare with a database.
I can find the employees that match the list.
How can I find the ones that are not in the database from this employee
list?
For example,
This is the Employee List (text file)
EmployeeID LastName FirstName
2 Fuller Andrew
3 Leverling Janet
11 Tool Mike
13 Oneal Jeff
Use Northwind
Select EmployeeID, LastName, FirstName
From Employees
Where EmployeeID IN
(
2,
3,
11,
13
)
Result:
2 Fuller Andrew
3 Leverling Janet
I do not know how to display the ones that don't match.
EmployeeID 11 and 13.
Expected Resut of the query would be:
11 Tool Mike
13 Oneal Jeff
Please help...Thanks a bunch.Select * from EmployeeTable
Where EmployeeID not in
(
Select EmployeeID from OtherEmployeeTable
)
If you want to display all Employees, even those who are not in the second
list
Select ET.EmployeeID, ISNULL(OET.EmployeeID,'That ones missing in the second
Table' from EmployeeTable ET
Left Join OtherEmployeeTable
ON
ET.EmployeeID = OET.EmployeeID
HTH, Jens Suessmeyer.
"SQL Apprentice" <mssqlworld@.yahoo.com> schrieb im Newsbeitrag
news:uABB83ZUFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a list of employees (+5000) that I use to compare with a database.
> I can find the employees that match the list.
> How can I find the ones that are not in the database from this employee
> list?
> For example,
> This is the Employee List (text file)
> EmployeeID LastName FirstName
> 2 Fuller Andrew
> 3 Leverling Janet
> 11 Tool Mike
> 13 Oneal Jeff
> Use Northwind
> Select EmployeeID, LastName, FirstName
> From Employees
> Where EmployeeID IN
> (
> 2,
> 3,
> 11,
> 13
> )
> Result:
> 2 Fuller Andrew
> 3 Leverling Janet
> I do not know how to display the ones that don't match.
> EmployeeID 11 and 13.
> Expected Resut of the query would be:
> 11 Tool Mike
> 13 Oneal Jeff
> Please help...Thanks a bunch.
>|||SQL Apprentice wrote:
> Hello,
> I have a list of employees (+5000) that I use to compare with a
> database. I can find the employees that match the list.
> How can I find the ones that are not in the database from this
> employee list?
> For example,
> This is the Employee List (text file)
> EmployeeID LastName FirstName
> 2 Fuller Andrew
> 3 Leverling Janet
> 11 Tool Mike
> 13 Oneal Jeff
> Use Northwind
> Select EmployeeID, LastName, FirstName
> From Employees
> Where EmployeeID IN
> (
> 2,
> 3,
> 11,
> 13
> )
> Result:
> 2 Fuller Andrew
> 3 Leverling Janet
> I do not know how to display the ones that don't match.
> EmployeeID 11 and 13.
> Expected Resut of the query would be:
> 11 Tool Mike
> 13 Oneal Jeff
> Please help...Thanks a bunch.
You cannot do this with a static list. You need to have a table, either
physical or derived. Here is an example using a derived table:
Select ListItem
From (
SELECT 2 AS ListItem
UNION ALL
SELECT 3
UNION ALL
SELECT 11
UNION ALL
SELECT 13) l LEFT JOIN Employees e
ON ListItem = EmployeeID
WHERE EmployeeID IS Null
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks for the good advice,
I imported the text file(employee list) into another table and did a
compare.
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:e0R1UMaUFHA.3944@.tk2msftngp13.phx.gbl...
> SQL Apprentice wrote:
> You cannot do this with a static list. You need to have a table, either
> physical or derived. Here is an example using a derived table:
> Select ListItem
> From (
> SELECT 2 AS ListItem
> UNION ALL
> SELECT 3
> UNION ALL
> SELECT 11
> UNION ALL
> SELECT 13) l LEFT JOIN Employees e
> ON ListItem = EmployeeID
> WHERE EmployeeID IS Null
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>