[RESOLVED] Query to exclude records

[chris.cavage]

For mysql...

Here's a query that gathers custom group information for a selected customer:

SELECT Group_ID FROM patient_group_join WHERE patient_group_join.Patient_ID= '292'

Here are ALL the possible groups that the customer CAN BE a part of:

SELECT `Group`, ID FROM groups
WHERE groups.Company_ID = '1'

How can I write a query that shows all the groups (groups.ID) that the customer is NOT a part of (show the groups.ID that are NOT IN the patient_group_join table).

Here's what I was working on:

SELECT `Group` FROM groups
LEFT JOIN 
(SELECT Group_ID FROM patient_group_join 
WHERE patient_group_join.Patient_ID= '292') S
ON groups.ID = S.Group_ID
WHERE S.Group_ID IS NULL AND groups.Company_ID = '1'

Unfortunately the query doesn't exclude the groups.ID that are in the join table under patient_group_join.Group_ID

[chris.cavage]

Got it:

SELECT `Group`, ID FROM groups
LEFT JOIN 
(SELECT Group_ID, Patient_ID FROM patient_group_join WHERE Patient_ID='292' AND Company_ID = '1') S
ON groups.ID = S.Group_ID
WHERE S.Group_ID IS NULL AND groups.Company_ID = '1'

I must have had the wrong Patient_ID to use as a test.