CODEWITHSUNDEEP
sqlsql-server
Erma
Erma

Reputation: 387

Eliminate data from one table w.r.t other table

I have 2 tables. say, Employee and User. EmployeeId, Status are common fields in both the tables. I need to check in Employee table and maintain data in User table.

For example:

Employee
-------------
EmployeeId    Status     EmpName
1111          Active     Mary
1112          Active     Joseph
1113          Inactive   Elizabeth 

User
-------------
EmployeeId    Status     UserName
1111          Active     Mary
1114          Active     Rachel

I would like to eliminate data in User table if it is already available in Employee table.

Result would be

TmpUser
-------------
EmployeeId    Status     UserName
1114          Active     Rachel

Please help to write query for the same.

Thanks in advance

Upvotes: 1

Views: 56

Answers (3)

Esperento57
Esperento57

Reputation: 17472

Try this:

--If you want only print you can do it
select f1.* 
from User f1 left outer join Employee f2 on f1.EmployeeId=f2.EmployeeId
where f2.EmployeeId is null

--If you want remove you can do it
delete f1 
from User f1 inner join Employee f2 on f1.EmployeeId=f2.EmployeeId

Upvotes: 1

SqlKindaGuy
SqlKindaGuy

Reputation: 3591

Another way to write it:

create table #Employee (empid int, sta varchar(50), empname varchar(50))
insert into #Employee
values

(1111        ,'Active',     'Mary'),
(1112        ,'Active',     'Joseph'),
(1113        ,'Inactive',   'Elizabeth') 


create table #user (empid int, sta varchar(50), username varchar(50))    
insert into #user

values
(1111         ,'Active','Mary'),
(1114         ,'Active','Rachel')

delete from #user 
where exists (select empid from #Employee b where #user.empid = b.empid)

Upvotes: 1

iminiki
iminiki

Reputation: 2573

You can write:

delete User
where EmployeeId in
(
     select EmployeeId from Employee
)

Upvotes: 2

Related Questions