SQL query example

Question

I have 3 tables users (id, name), types (id, name) and user_type (users_id, types_id)

I would like to write a SQL query that gets all the users that DO NOT have a type. I know this is probably easy but I can't get my head around it.

Any help

Answer 1

As another alternative you can use EXISTS or NOT EXISTS

SELECT *
FROM users
WHERE NOT EXISTS (SELECT * FROM user_type WHERE users.USER_ID = user_type.USER_ID)

another alternative is to use left join and check nulls

SELECT *
FROM users AS s
LEFT JOIN user_type AS ut
ON s.USER_ID = ut.user_id
WHERE ut.USER_ID IS null

Documentation on EXISTS and NOT EXISTS can be found here http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html

Answer 2

You may try something like this

SELECT * FROM users
WHERE users_id NOT IN (SELECT users_id FROM user_type)

Answer 3

How about:

SELECT * FROM users
WHERE users_id NOT IN
    (SELECT users_id FROM user_type)

Answer 4

SELECT users.name
FROM users JOIN user_types ON users.id = user_types.users_id
WHERE user_types.types_id != yourID