How to exclude entire record if part meets criteria

[4656rp]

Howdy,

Here's my dilemma, I have a dataset of patients and each patient can have several diagnosis. I want to make a table that excludes patients if one (of many diagnosis) is in a lookup table of excluded diagnoses. Currently, I link both tables and select is null criteria in the lookup table. This doesn't work because I get all the other diagnoses.

Here's a sample....
Data table
Name Diagnosis
PMorris 123.1
PMorris 124.2
PMorris 127.5
MPhillips 129.0
MPhillips 133.4
MPhillips 185.0

Lookup of excluding diagnoses
diagnosis
168.4
185.0
201.7

Resulting table:
Name Diagnosis
PMorris 123.1
PMorris 124.2
PMorris 127.5

All ideas are greatly appreciated.

 

[PHV]

You may try something like this:
SELECT A.Name, A.Diagnosis FROM [data table] A
WHERE A.Name Not In (SELECT B.Name FROM [data table] B INNER JOIN [excluding diagnoses] D ON B.Diagnosis=D.diagnosis)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244