Mysql exclude records

mysql, record

Solution

SELECT
    user.ID, user.FirstName, user.LastName
FROM
    user
WHERE
    user.ID NOT IN (
                    SELECT ID FROM role WHERE role.RoleID = '3'
                   )

This uses whats known as a subquery in MySQL. The subquery in the WHERE clause will select all the IDs (such as Peter) who have a RoleID of 3. It will then exclude those ID's (Peter), using `NOT IN()`, from the selection of users.

Problem

I have two tables, user and role, one user can have more than 1 role ``` user: ID | FIRSTNAME | LASTNAME | etc.. 1 | PETER | Blomp | role: ID | ROLEID | USERID (which is user ID) 70 | 5 | 1 (peter) 71 | 2 | 1 ``` What I have to do and cant figure out is, how can retrieve data of users whos roleid is not even to some integer, for. ex. user PETER can have roleID's 5 and 2, what i am trying to get is that IF Peter has roleid 3, he excludes from resultset, no matter if he has roleid 5.

Original source