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]
