mysql - SELECT query for three connected tables -

my table structure looks following:

 subversions(                 id int(10) auto_increment,                 version_id int(10)                 name varchar(16) not null,                  primary key (id),                 foreign key (version_id) references versions(id)             ); /*      ^     many|                | many         v */ users(     id int(10) auto_increment,     name varchar(16) not null,      password varchar(32) not null,     primary key (id), ); 

with junction table

subversions_users_conjuction(     id int(10) auto_increment,     subversion_id varchar(16) not null,      user_id varchar(32) not null,     primary key (id),     foreign key (subversion_id) references subversions(id),     foreign key (user_id) references users(id)     ); 

my problem designing query selecting users "users" not in subversions_users_conjuction with subversion_id = specificid , without getting

so following:

//query other users $res2 = mysqli_query($conn,"select distinct i.id, i.username users                     not in                      select distinct u.id, u.username users u                     inner join subversion_user on u.id = subversion_user.user_id                     subversion_user.subversion_id = '$subversionid'"); 

but , 1 doesn't work sql error says:

mysql error: have error in sql syntax; check manual corresponds mysql server version right syntax use near 'not in                          select distinct u.id, u.username users u                         inner joi' @ line 2 

you have 3 tables, , want join them. additionally want find entries 'missing' normal inner join. in case want outer join. (unfortunately mysql doesn't include full outer join)

my sqlfiddle

my test code:

create table subversion (    id int(10) auto_increment,    name varchar(16) not null,     primary key (id) );  insert subversion (name) values ('terror'); insert subversion (name) values ('mayhem'); insert subversion (name) values ('bribery'); insert subversion (name) values ('eating ice cream'); insert subversion (name) values ('unrest'); insert subversion (name) values ('infiltration');  create table users(     id int(10) auto_increment,     name varchar(16) not null,      password varchar(32) not null,     primary key (id) );  insert users (name, password) values ('adam', '12345'),('bill', '78903'), ('chandra', '56565'), ('david', '34543'), ('edwina', 'abcde'), ('faheem', 'jklab'), ('george', 'vfghn'), ('hank', '2ws3e') ;  create table subversions_users_conjuction(     id int(10) auto_increment,     subversion_id int(10) not null,      user_id int(10) not null,     primary key (id),     foreign key (subversion_id) references subversion(id),     foreign key (user_id) references users(id)     );  insert subversions_users_conjuction (user_id, subversion_id) values (1,1), (2,2), (3,1), (4,4), (5,5); 

my left outer query

select *  users left outer join subversions_users_conjuction  on users.id = subversions_users_conjuction.user_id left outer join subversion on subversion.id = subversions_users_conjuction.subversion_id  subversion_id null; 

results:

| id |   name | password |     id | subversion_id | user_id |     id |   name | |----|--------|----------|--------|---------------|---------|--------|--------| |  6 | faheem |    jklab | (null) |        (null) |  (null) | (null) | (null) | |  7 | george |    vfghn | (null) |        (null) |  (null) | (null) | (null) | |  8 |   hank |    2ws3e | (null) |        (null) |  (null) | (null) | (null) | 

things learned here:

• database id naming convention
• inner / outer joins
• is null / not null operators
• sqlfiddle.com