Mattias Nordstrom wrote:
No, apparently that returns all users. I'd like it to return only the ones
that are not in group_id=1. i.e. should not return the ones that are in
group_id=1, which it does now.
There was one error in my query, which does give syntax error atleast on
MySQL version 3.x, but after fixing that, it should work correctly.
Let me show you (I only put user_id and user_surname to users table,
other columns should follow them automaticly, so adding columns won't
make a difference.):
-----start from mysql console----------------------------------
mysql> create table users(user_id int, user_surname varchar(255) );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into users values(1,'a'),(2,'b'),(3,'c'),
-> (4,'d'),(5,'e'),(6,'f'),(7,'g');
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> create table linkage(user_id int, group_id int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into linkage values(1,1),(1,2),(1,3),(2,2),
-> (2,3),(2,4),(3,1),(4,2),(6,1),(7,2),(7,4);
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> select * from users;
+---------+--------------+
| user_id | user_surname |
+---------+--------------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
| 6 | f |
| 7 | g |
+---------+--------------+
7 rows in set (0.00 sec)
mysql> select * from linkage;
+---------+----------+
| user_id | group_id |
+---------+----------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 2 |
| 2 | 3 |
| 2 | 4 |
| 3 | 1 |
| 4 | 2 |
| 6 | 1 |
| 7 | 2 |
| 7 | 4 |
+---------+----------+
11 rows in set (0.00 sec)
mysql> select users.user_id, users.user_surname
-> from users
-> left join linkage on linkage.user_id=users.user_id
-> and linkage.group_id=1
-> where linkage.user_id is null;
+---------+--------------+
| user_id | user_surname |
+---------+--------------+
| 2 | b |
| 4 | d |
| 5 | e |
| 7 | g |
+---------+--------------+
4 rows in set (0.00 sec)
-----end from mysql console----------------------------------
We had 7 users, but only 4 of them are returned, none of the users which
are in group 1 are returned. Am I misunderstanding something or have I
missed anything?
Can you provide a similar test case that shows how this doesn't work?