Need help with MySQL-Query

[merendo]

Hey!

Merendo has a little problem with some my MySQL-query. Maybe you can help me.

Image, I had one table full of groups. Usergroups to be precise. Now there is another table full of users. Each of this users can be a member of any of the groups. (See where I am going? This is the Unix-multiuser system). A third table tells me which user is a member of which group. For example:

Code: Select all

Table users: ID, Name, Adress, Phone, ...
Table usergroups: ID, Name, Description, Blah Blah ...
Table groups_members: ID, UserID, GroupID

Now I need a query, that will give me all users, that are NOT a member of a specifig group... I tried this one:

Code: Select all

SELECT `users`.`ID` FROM `users`,`groups_members` WHERE (`users`.`ID` != `groups_members`.`UserID`) AND (`groups_members`.`GroupID` = '[Some ID of a group...]');

But it returns just rubbish.

Any idea how I could make this one work?

merendo

[GedB]

Merendo,

You're problem is that a standard join will only include fields in both tables.

'FROM 'users', 'groups_members' creates a standard inner join. Only records that exist in both table will be included. The records you are interested in are removed.

You need to use a left join, as described in the MySql reference manual:

http://dev.mysql.com/doc/mysql/en/join.html

The ON conditional is any conditional expression of the form that can be used in a WHERE clause.

If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find records in a table that have no counterpart in another table:

Code: Select all

mysql> SELECT table1.* FROM table1
    ->        LEFT JOIN table2 ON table1.id=table2.id
    ->        WHERE table2.id IS NULL;

This example finds all rows in table1 with an id value that is not present in table2 (that is, all rows in table1 with no corresponding row in table2). This assumes that table2.id is declared NOT NULL. See Section 7.2.9, “How MySQL Optimizes LEFT JOIN and RIGHT JOIN”.