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/[EMAIL PROTECTED]
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/[EMAIL PROTECTED]
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/[EMAIL PROTECTED]
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/[EMAIL PROTECTED]
