Delete where 2 fields do not match

I need to be able to delete records from 1 table that do not match 2 fields
on a 2nd table. I was trying to get the SELECT to work first to find the
records that are not full matches. Below is my code and would appreciate
any help. I thought of maybe a NOT IN() or NOT EXISTS() kind of code, but
was not sure. Thanks.
SELECT dbo.ClientWorkerStatus.ClientID, dbo.ClientWorkerStatus.WorkerID
FROM dbo.ClientWorkerStatus, ClientWorkerLink
WHERE (dbo.ClientWorkerStatus.ClientID = dbo.ClientWorkerLink.ClientID AND
dbo.ClientWorkerStatus.WorkerID <> dbo.ClientWorkerLink.WorkerID)
The problem with this is it gives me every record that does not match on
WorkerID. What I want is only those records that are on the
ClientWorkerStatus table that do not have exact match between ClientID and
WorkerID.David,
Can you post the DDL, sample data (INSERTs) and the desired resultset?
HTH
Jerry
"David" <dlchase@.lifetimeinc.com> wrote in message
news:OYqCKc2yFHA.3180@.TK2MSFTNGP14.phx.gbl...
>I need to be able to delete records from 1 table that do not match 2 fields
>on a 2nd table. I was trying to get the SELECT to work first to find the
>records that are not full matches. Below is my code and would appreciate
>any help. I thought of maybe a NOT IN() or NOT EXISTS() kind of code, but
>was not sure. Thanks.
> SELECT dbo.ClientWorkerStatus.ClientID, dbo.ClientWorkerStatus.WorkerID
> FROM dbo.ClientWorkerStatus, ClientWorkerLink
> WHERE (dbo.ClientWorkerStatus.ClientID = dbo.ClientWorkerLink.ClientID AND
> dbo.ClientWorkerStatus.WorkerID <> dbo.ClientWorkerLink.WorkerID)
> The problem with this is it gives me every record that does not match on
> WorkerID. What I want is only those records that are on the
> ClientWorkerStatus table that do not have exact match between ClientID and
> WorkerID.
>|||Try this,
SELECT ClientID, WorkerID
FROM ClientWorkerStatus C
WHERE (ClientID IN (SELECT ClientID
FROM ClientWorkerLink
WHERE (ClientID = C.ClientID) AND (WorkerID <>
C.WorkerID)))
"David" wrote:

> I need to be able to delete records from 1 table that do not match 2 field
s
> on a 2nd table. I was trying to get the SELECT to work first to find the
> records that are not full matches. Below is my code and would appreciate
> any help. I thought of maybe a NOT IN() or NOT EXISTS() kind of code, but
> was not sure. Thanks.
> SELECT dbo.ClientWorkerStatus.ClientID, dbo.ClientWorkerStatus.WorkerID
> FROM dbo.ClientWorkerStatus, ClientWorkerLink
> WHERE (dbo.ClientWorkerStatus.ClientID = dbo.ClientWorkerLink.ClientID AND
> dbo.ClientWorkerStatus.WorkerID <> dbo.ClientWorkerLink.WorkerID)
> The problem with this is it gives me every record that does not match on
> WorkerID. What I want is only those records that are on the
> ClientWorkerStatus table that do not have exact match between ClientID and
> WorkerID.
>
>|||Try,
delete dbo.ClientWorkerStatus
where not exists (
select *
from dbo.ClientWorkerLink as a
where a.ClientID = dbo.ClientWorkerStatus.ClientID
and a.WorkerID = dbo.ClientWorkerStatus.WorkerID
)
AMB
"David" wrote:

> I need to be able to delete records from 1 table that do not match 2 field
s
> on a 2nd table. I was trying to get the SELECT to work first to find the
> records that are not full matches. Below is my code and would appreciate
> any help. I thought of maybe a NOT IN() or NOT EXISTS() kind of code, but
> was not sure. Thanks.
> SELECT dbo.ClientWorkerStatus.ClientID, dbo.ClientWorkerStatus.WorkerID
> FROM dbo.ClientWorkerStatus, ClientWorkerLink
> WHERE (dbo.ClientWorkerStatus.ClientID = dbo.ClientWorkerLink.ClientID AND
> dbo.ClientWorkerStatus.WorkerID <> dbo.ClientWorkerLink.WorkerID)
> The problem with this is it gives me every record that does not match on
> WorkerID. What I want is only those records that are on the
> ClientWorkerStatus table that do not have exact match between ClientID and
> WorkerID.
>
>|||Got it! Thanks.
*** Sent via Developersdex http://www.examnotes.net ***