select records not in a *particular* many-to-many relationship

Hey all,

I've checked the archives and found an explanation as to how the check
if a record is not in a many-to-many table. The answer to that is
somewhat simple and clear to me. But here's my problem: how do you
check if a record doesn't have a *particular* many-to-many
relationship? As in, let's say I have three tables: users, groups, and
users_groups linking the two in a many-to-many relationship. Now let's
say that I want to select all users who are not in the group "Group1" --
that is, that user may have entries in the users_groups table, but they
would be for other groups, not "Group1."

One more thing: this is easily done with subqueries, but for performance
reasons, I need to do it with explicit joins. Anyone know how I can do
this?

Thanks,
marco

--
Marco Carbone
Webmaster/Web Developer
Committee to Regulate and Control Marijuana
http://www.regulatemarijuana.org


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: select records not in a *particular* many-to-many relationship

Marco,
> I've checked the archives and found an explanation as to how the check
> if a record is not in a many-to-many table. The answer to that is
> somewhat simple and clear to me. But here's my problem: how do you
> check if a record doesn't have a *particular* many-to-many
> relationship? As in, let's say I have three tables: users, groups,
> and users_groups linking the two in a many-to-many relationship. Now
> let's say that I want to select all users who are not in the group
> "Group1" -- that is, that user may have entries in the users_groups
> table, but they would be for other groups, not "Group1."
>
> One more thing: this is easily done with subqueries, but for
> performance reasons, I need to do it with explicit joins. Anyone know
> how I can do this?
A quick way is with a temp table, eg ...

DROP TEMPORARY TABLE IF EXISTS j;
CREATE TEMPORARY TABLE j
SELECT
u.userID,
ug.groupID
FROM users u
LEFT JOIN usergroups u USING (userID);

SELECT j.partyID AS NotGroup1
FROM j
LEFT JOIN usergroups ug
ON j.userID=ug.userID AND ug.groupID=1
WHERE ug.group IS NULL;

DROP TEMPORARY TABLE j;

PB

-----
>
> Thanks,
> marco
>


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.3/317 - Release Date: 4/18/2006


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: select records not in a *particular* many-to-many relationship

>
>
>> I've checked the archives and found an explanation as to how the
>> check if a record is not in a many-to-many table. The answer to that
>> is somewhat simple and clear to me. But here's my problem: how do
>> you check if a record doesn't have a *particular* many-to-many
>> relationship? As in, let's say I have three tables: users, groups,
>> and users_groups linking the two in a many-to-many relationship. Now
>> let's say that I want to select all users who are not in the group
>> "Group1" -- that is, that user may have entries in the users_groups
>> table, but they would be for other groups, not "Group1."
>>
>> One more thing: this is easily done with subqueries, but for
>> performance reasons, I need to do it with explicit joins. Anyone
>> know how I can do this?
>

SELECT u.userID
FROM users u
LEFT JOIN user_groups ug
ON u.userID = ug.userID and ug.groupID = 'Group1'
WHERE ug.groupID IS NULL


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: select records not in a *particular* many-to-many relationship

gerald_clark wrote:
>>
>>
>>> I've checked the archives and found an explanation as to how the
>>> check if a record is not in a many-to-many table. The answer to
>>> that is somewhat simple and clear to me. But here's my problem: how
>>> do you check if a record doesn't have a *particular* many-to-many
>>> relationship? As in, let's say I have three tables: users, groups,
>>> and users_groups linking the two in a many-to-many relationship.
>>> Now let's say that I want to select all users who are not in the
>>> group "Group1" -- that is, that user may have entries in the
>>> users_groups table, but they would be for other groups, not "Group1."
>>>
>>> One more thing: this is easily done with subqueries, but for
>>> performance reasons, I need to do it with explicit joins. Anyone
>>> know how I can do this?
>>
>
> SELECT u.userID
> FROM users u
> LEFT JOIN user_groups ug
> ON u.userID = ug.userID and ug.groupID = 'Group1'
> WHERE ug.groupID IS NULL
>
>
But I have three tables, not two. In other words, I have the name
'Group1,' but not the id.

--
Marco Carbone
Webmaster/Web Developer
Committee to Regulate and Control Marijuana
http://www.regulatemarijuana.org


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org