Left Join to exclude records

[Chuck712]

I am having some issues trying to get a query to exclude some individuals from a mailing list. I have 3 tables, one for clients and 1 for program participation and 1 for individuals. Individuals can participate on multiple programs, but I am trying to get only those who have not particpated in the current program.
Tables look like this: (Very simplified)
client_table
h_13Key H13UserID H13FirstName H13LastName
DD6493DD 145 Joe Smith
E08CE1F8 154 Jasmin Smith
9AA95241 127 Susan Someone
DD6493DD 145 Joe Mama
11611C0A 130 Walden Pond

program_results_table
H11userID H11ProgramID H_11Hey
145 101 FF4B1F85
154 101 33D43800
127 103 A33DF48D
127 101 F3FBD1A8
145 102 E678B6BC
145 103 7F7AC520
130 101 E96AFD6A
130 103 6BC57477

Individuals
cst_key cst_firstname cst_lastname
DD6493DD Joe Smith
E08CE1F8 Jasmin Smith
9AA95241 Susan Someone
DD6493DD Joe Mama
11611C0A Walden Pond
5609RTEE Another Guy
R68793AZ Busta Move
90FJKLR76 One More

I need everyone who did not particpate in program 103 or has not participated at all.

Here would be the rough query:
SELECT
h_13Key,
H13UserID,
H13FirstName,
H13LastName,
H11userID,
H11ProgramID,
H_11Hey


FROM client_table
left join individuals on h13Key = cst_key
left join (SELECT H11userID,h11_delete_flag,MAX(H11ProgramID)as progID,h11_resultid FROM program_results_table
Group by H11userID,
h11_delete_flag,
H11ProgramID,
h11_hraresultid
HAVING MAX(H11ProgramID) <'103') as a

on a.H11userID=H13UserID and h11_delete_flag = 0
Where H11ProgramID <> '103 or H11ProgramID is null

I am still getting the records for those with program ID 101 or 102.
I have cut this up to simplify so I hope I didn't find and replace the wrong thing. Any suggestions?

 

[markros]

Try using WHERE NOT EXISTS (..) to exclude clients with no 103 program and no programs at all.

If you want more detailed answer, please post the CREATE table statements and insert statements in addition to this narrative.

PluralSight Learning Library

 

[Chuck712]

Thanks. I did that earlier. These are examples of existing tables, so I don't have create table or insert statements.
The actual query is fairly large, since it has ties to many other tables. I was hoping for a simple answer.

 

[markros]

The simple answer is in using WHERE NOT EXISTS.

Here would be the rough query:
SELECT 
h_13Key,
H13UserID,
H13FirstName,
H13LastName,
H11userID,
H11ProgramID,
H_11Hey


FROM client_table C
left join individuals on h13Key = cst_key
WHERE NOT EXISTS (select 1 from Program_Result_Table P

where P.H11UserID = C.H13UserID and P.H11ProgramID = '103')

The above will select all clients who never participated in the 103 program.

Is it the intent of your query?

PluralSight Learning Library

 

[Chuck712]

Thanks so much. I am going to try it. Yes, I need those who never participated or who never particpated in program 103