March 1, 2007 at 4:15 am
Hi everyone.
I have 2 tables, one called employee (columns - ‘employeeID’,’emprate’) and the other employee_rates(columns -‘employeeID’,’emprate’).I need to retrieve every 'emprate' from employee_rates table that is not in employee table for each employeeID.
March 1, 2007 at 5:37 am
SELECT ER.*
FROM employee_rates ER
LEFT JOIN employee E
ON ER.employeeID = E.employeeID
AND ER.emprate = E.emprate
WHERE E.employeeID IS NULL
or
SELECT *
FROM employee_rates ER
WHERE NOT EXISTS (
SELECT *
FROM employee E
WHERE E.employeeID = ER.employeeID
AND E.emprate = ER.emprate
)
March 1, 2007 at 12:02 pm
How about using except?
create
table #employee (EmployeID int, Emprate int)
create
table #Employee_Rates (EmployeeID int, Emprate int)
insert
into #employee values (1, 1)
insert
into #employee values (1, 2)
insert
into #employee values (2, 3)
insert
into #employee values (2, 1)
insert
into #Employee_Rates values (1,1)
insert
into #Employee_Rates values (1,4)
insert
into #Employee_Rates values (1,3)
insert
into #Employee_Rates values (2,1)
insert
into #Employee_Rates values (2,4)
select
* from #employee_Rates
except
select
* from #employee
Will return:
EmployeeID Emprate
----------------------------------------
1 3
1 4
2 4
(3 row(s) affected)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply