I have 3 different tables:
contacts
groups
contact_rel_groups (link table)
In the application I am building, contacts have the ability to be part of multiple groups (or groups of contacts). The respective contact_id and group_id are my primary keys of my contact_rel_groups table, or my link table.
I have easily been able to write a command to select contacts that are in a group, my working code for that is:
SELECT
contacts.contact_id,
contacts.contact_fname,
contacts.contact_lname,
contacts.contact_mobile,
groups.group_name
FROM
contacts
JOIN contacts_rel_groups ON (contacts.contact_id = contacts_rel_groups.contact_id)
JOIN groups ON (contacts_rel_groups.group_id = groups.group_id)
WHERE
contacts.user_id=1
AND
groups.group_id=1
The 1 value for contacts.user_id=1 and groups.group_id=1 are passed by session variables, but for the purpose of my question I'm selecting contacts that belong to user_id=1 and only contacts that are in group_id=1. Everything works great with the above statement; however where my issue comes into play is when I try and select all the contacts that are not in group=1.
So basically my GUI displays two grids, one on the left showing "Current Contacts" in the group, and another showing "Available Contacts" in the other grid; Essentially "Available Contacts" are all of the contacts that are not in group 1.
I've tried this statement (among a dozen others) to no avail:
SELECT
contacts.contact_id,
contacts.contact_fname,
contacts.contact_lname,
contacts.contact_mobile,
FROM
groups
JOIN contacts_rel_groups ON (groups.group_id = contacts_rel_groups.group_id)
JOIN contacts ON (contacts_rel_groups.contact_id <> contacts.contact_id)
WHERE
contacts.user_id=1
AND
groups.group_id=1
The result is all of the contacts in the contacts table plus duplicates... so I tried a SELECT DISTINCT and I get all of the contacts in the contacts table without duplicates... How can I write the statement to get the reverse results of my first statement, or all contacts not in group_id=1?
Your help is much appreciated!