Query to Find Unmatched Data

[meme lia]

hiii...

I have user data in a two column table..

table A
id___name
01___jason
02___robert
03___cecil
04___linda
05___dave

table B
id___employ
02___manager
03___teller
04___teller

i want to compare it.
i want to choose the one in the rows that does not exist in table B..

for example : 01__manager from table B

thx

[Rabbit]

Please don't double post your questions.

Three things.
1) To find records that exist in a table x that don't exist in another table y, you can outer join the tables and filter for the nulls in the joined field(s) in y.

2) In your example, there is no record of 01_Manager and there is no way of knowing that 01 is a manager because no such information exists. All that can be said is that record 01 does not exist in table B. What position they hold is irrelevant.

3) There is no need to separate out these columns into different fields unless one person can hold multiple positions.

[nbiswas]

Hi,
As Rabbit has rightly pointed out, your question is a little unclear. However, I am assuming the below tables

TableA

ID	Name
1	Jason
2	Robert
3	Cecil
4	Linda
5	Dave

And

TableB

ID	Name
1	Manager
2	Teller
3	Teller
4	Linda
5	Dave

The below ddls will help to create the test environment

Declare @tableA table(ID int Identity,Name varchar(20))
Insert into @tableA 
Select 'Jason' union all Select 'Robert' Union all select 'Cecil' Union all select 'Linda'
Union all select 'Dave'
Select * from @tableA 

Declare @tableB table(ID int Identity,Name varchar(20))
Insert into @tableB 
Select 'Manager' union all Select 'Teller' Union all select 'Teller' Union all select 'Linda'
Union all select 'Dave'
Select * from @tableB

Objective

We are interested to find out those employees that are not in table A. So the desired output in this case will be

ID	Name
1	Manager
2	Teller
3	Teller

Query 1: Using Except

Select * from @tableB except(Select * from @tableA )

Query 2: Using Not In

Select B.ID,B.Name 
From @tableB B
Where B.Name Not In(Select Name from @tableA)

Query 3: Using Left Join And filter condition for Nulls

Select B.ID,B.Name 
From @tableB B Left Join @tableA A
On A.Name = B.Name
Where A.Name Is Null

Query 4: Using Not Exists

select B.ID,B.Name 
from @tableB B 
where not exists ( Select Name from @tableA A where A.Name = B.Name)

Hope this helps.

Kindly let me know in case of any concern