My database looks something like this, in order of user, userteam (connection table) and team:
Usertable UserTeam Teamtable
+--------+------+ +--------+--------+ +--------+-------+
| userid | name | | userid | teamid | | teamid | name |
+--------+------+ +--------+--------+ +--------+-------+
| 1 | eric | | 1 | 1 | | 1 | awsm |
| 2 | john | | 1 | 2 | | 2 | doe |
| 3 | carl | | 2 | 1 | | 3 | empty |
+--------+------+ | 3 | 1 | +--------+-------+
+--------+--------+
How do I select all users that IS NOT in a team, and echo them out only once? I've tried doing it with the usertable, but then it will echo for instance, if I try and select all members not in team 3:
SELECT userid FROM userteam WHERE teamid!=3;
SELECT * FROM user WHERE userid='$previousSql';
1 eric
1 eric
2 john
3 carl
What I'd like instead is:
1 eric
2 john
3 carl
I'm making an add members function, and I'd very much not like everyone to show up more than once.. Any directions, help or guiding would be much appreciated.
This should do it:
SELECT u.userid, u.name FROM Usertable u
INNER JOIN UserTeam ut ON ut.userid = u.userid
INNER JOIN Teamtable tt ON tt.teamid = ut.teamid
WHERE tt.teamid != 3
GROUP BY u.userid, u.name;
SELECT userid, name FROM usertable
JOIN userteam ON userteam.userid = usertable.userid
JOIN teamtable on teamtable.teamid = userteam.teamid
WHERE teamtable.teamid = 3;
The SQL above should do the proper joins between all the tables and then will only list the userid/names of people who have a teamid of 3 in the teamtable
EDIT* Removed ! from WHERE clause.
add
GROUP BY name
to the query