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

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