Help with unmatched query via join

[PRMiller2]

Fellow Tek-Tippers, I'm trying to wrap my head around how to accomplish this and would love some help. I have three tables:

tblAudit
lonAuditID_pk
lonClientID_fk
txtPlanCode
intCustom
intPass

tblCustomPlanCodes
lonCustomPlanCodeID_pk
txtCustomPlanCode

tblClientsCustomPlanCodes
lonClientsCustomPlanCodesID_pk
lonClientID_fk
lonCustomPlanCodeID_fk

There are potentially hundreds of thousands of records in tblAudit. There are a wide variety of txtPlanCodes in that field, and the vast majority of them I want to ignore. However, for records in tblAudit where intCustom = 1, I want to do some validation.

Any txtPlanCode in tblAudit having an intCustom = 1 needs to have a corresponding record in in tblClientsCustomPlanCodes. This table designates a relationship between a CustomPlanCode and a Client. If tblAudit.txtPlanCode does not have a corresponding record in tblClientsCustomPlanCodes where tblAudit.lonClientID_fk is equal to tblClientsCustomPlanCodes.lonClientID_fk AND tblAudit.intCustom = 1, then I'll update the intPass field in the first table to a 0 for fail.

I'm struggling building the Select table. I'm envisioning something like the following:

SELECT a.intPass
FROM tblAudit AS a
WHERE a.txtPlanCode NOT IN
 (SELECT b.txtCustomPlanCode
  FROM tblCustomPlanCodes AS b
  LEFT JOIN tblClientsCustomPlanCodes AS c 
  ON b.lonCustomPlanCodeID_pk = c.lonCustomPlanCodeID_fk)

That's about the extent of what I've come up with, but, as you can see, I'm missing some of the essential criteria. Any help would be greatly appreciated!

 

[PHV]

What about this ?

UPDATE tblAudit a LEFT JOIN tblClientsCustomPlanCodes c ON a.lonClientID_fk = c.lonClientID_fk
SET a.intPass = 0
WHERE a.intCustom = 1 AND c.lonClientID_fk IS NULL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PRMiller2]

Thanks PHV. Spot on, as always!