We help IT Professionals succeed at work.

MySQL Select Non Equal records using link table

woodenconsulting
woodenconsulting asked
on
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!


Comment
Watch Question

Commented:
Don't understand much ... But I hope this may solve ...

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


--
yruz!

Author

Commented:
yruz,

Your query does not work, let me see if I can explain things a bit better....

For starters, I'm building an application using ExtJS, PHP and MySQL... In the part of my application that is relevant, I allow the user to edit the clients that are part of a group through a drag and drop system. I have 3 different MySQL tables called: contacts, groups,  contacts_rel_groups. A contact can be part of any group, so for instance; I could have groups called: Pizza, Alchohol, Soda Pop and a single client could be part of each of those groups. This relationship is created in the link table which is called contacts_rel_groups. To give you a better idea of what I'm talking about, the structure of my tables is below:

CONTACTS
contact_id (int) - primary key
contact_fname (varchar)
contact_lname(varchar)
contact_mobile(int)

GROUPS
group_id (int) - primary key
group_name (varchar)

CONTACTS_REL_GROUPS
contact_id (int) - primary key
group_id (int) - primary key

As stated above, I can easily grab the contacts that are inside of a group using the statement also outlined in my first post. The difficulty is grabbing every single contact that does not belong to the group that is being displayed.

I need to know how I would query that based on my table structure. My statement for querying contacts in a group is in my first post, I need to query contacts not in a group based of my table structures... your help is much appreciated!

Author

Commented:
Here is another way to write my first statement which ONLY SELECTS CONTACTS IN A GROUP:

SELECT
contacts.contact_id,
contacts.contact_fname,
contacts.contact_lname,
contacts.contact_mobile,
groups.group_name
FROM
contacts, contacts_rel_groups
JOIN groups ON (contacts_rel_groups.group_id = ' . $group_id . ')
WHERE
contacts.contact_id = contacts_rel_groups.contact_id
AND
contacts.user_id=' . $_SESSION['user_id']

I'M NOT SURE IF THIS WILL HELP EXPLAIN WHAT I'M DOING OR HELP SOMEONE TO WRITE A QUERY TO SELECT ONLY THE CONTACTS THAT ARE NOT IN THE GROUP or $group_id which is 1 in my first example.

Author

Commented:

The only time I use experts exchange is for extremely complex problems which I always figure out on my own... This time the solution to this problem is time sensitive, I will PayPal $100 to anyone who answers my question by Monday... Let's see if a money reward can make this site work... that would be a first for me! Thanks in advance!

Commented:
from this statement : "The difficulty is grabbing every single contact that does not belong to the group that is being displayed." ,   I suppose you don't consider "contacts.user_id=' . $_SESSION['user_id']. '" in this case...

if so, Try this :

' SELECT
contacts.contact_id,
contacts.contact_fname,
contacts.contact_lname,
contacts.contact_mobile,
groups.group_name
FROM
contacts INNER JOIN contacts_rel_groups ON (contacts.contact_id = contacts_rel_groups.contact_id)
INNER JOIN groups ON ( contacts_rel_groups.group_id = groups.group_id )
WHERE
groups.group_id <> '.$group_id


Hope this helps..

--
YRuZ!

Author

Commented:
I would need to consider the user_id session because it pertains to the user login... There are multiple users in this system each with their own contacts, which is where the user_id session comes into play... Nonetheless I will try your statement considering the user_id at my office tomorrow.... I will report back with my findings...
After going over this in my head for a second I realized the groups table didn't need to be involved in my query, making it much simpler. What I needed to do was create a default record in contacts_rel_groups for every contact where the group_id was equal to 0. This allowed me to use this query which works beautifully:

SELECT DISTINCT
contacts.contact_fname,
contacts.contact_lname,
contacts.contact_mobile
FROM
contacts,
contacts_rel_groups
WHERE
contacts.contact_id =  contacts_rel_groups.contact_id AND
contacts_rel_groups.group_id <>  '1' AND
contacts.user_id =  '1'