[RESOLVED] Query to exclude records

For mysql...

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

Code:

---

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:

Code:

---

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:

Code:

---

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

Got it:

Code:

---

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.