List:General Discussion« Previous MessageNext Message »
From:Marco Carbone Date:April 18 2006 5:37pm
Subject:Re: select records not in a *particular* many-to-many relationship
View as plain text  
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

Thread
select records not in a *particular* many-to-many relationshipMarco Carbone18 Apr
  • Re: select records not in a *particular* many-to-many relationshipPeter Brawley18 Apr
    • Re: select records not in a *particular* many-to-many relationshipgerald_clark18 Apr
      • Re: select records not in a *particular* many-to-many relationshipMarco Carbone18 Apr