MySQL Select Non Equal records using link table
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.contac
JOIN groups ON (contacts_rel_groups.group
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_
JOIN contacts ON (contacts_rel_groups.conta
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!
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.contac
JOIN groups ON (contacts_rel_groups.group
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_
JOIN contacts ON (contacts_rel_groups.conta
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!
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.contac
JOIN groups ON (contacts_rel_groups.group
WHERE
contacts.user_id =1
AND
groups.group_id <> 1
--
yruz!
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.contac
JOIN groups ON (contacts_rel_groups.group
WHERE
contacts.user_id =1
AND
groups.group_id <> 1
--
yruz!
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!
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!
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
WHERE
contacts.contact_id = contacts_rel_groups.contac
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.
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
WHERE
contacts.contact_id = contacts_rel_groups.contac
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.
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!
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.contac
INNER JOIN groups ON ( contacts_rel_groups.group_
WHERE
groups.group_id <> '.$group_id
Hope this helps..
--
YRuZ!
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.contac
INNER JOIN groups ON ( contacts_rel_groups.group_
WHERE
groups.group_id <> '.$group_id
Hope this helps..
--
YRuZ!
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.contac
contacts_rel_groups.group_
contacts.user_id = '1'
SELECT DISTINCT
contacts.contact_fname,
contacts.contact_lname,
contacts.contact_mobile
FROM
contacts,
contacts_rel_groups
WHERE
contacts.contact_id = contacts_rel_groups.contac
contacts_rel_groups.group_
contacts.user_id = '1'
Premium Content
You need an Expert Office subscription to comment.Start Free Trial