[RESOLVED] MySQL query - find people not on the project

[nunomira]

Having a table with the following fields

id, id_user, id_project

how do I find the users which are not assigned to a project?

note:
a user can be assigned to several projects and a project can have several users

[g0dzuki]

Probably be better to have two tables, but maybe something like:

SELECT id_project
WHERE id_user = ''

.. would get all projects where id_user is blank.

• Chris

[Piranha]

Do you mean that are not assigned to any project, then it is probably impossible with only this table, the user table is needed as well. If you mean not assigned to a specific projectid then you can do the following:

SELECT user FROM table WHERE user NOT IN (SELECT user FROM table WHERE project = $project)

If you want users that is not assigned to any project you do it in a simular way:

SELECT user.id FROM user WHERE user.id NOT IN (SELECT DISTINCT user FROM table)

Or you can do it with join:

SELECT user.id, projectid FROM user LEFT JOIN table ON user.id = table.user WHERE projectid IS NULL

These queries are untested, so there might be some small syntax problem.

[nunomira]

Hi Piranha,

Yes, I meant "how do I find the users which are not assigned to a specific project?" - sorry.

That's it, thanks!

SELECT user FROM table WHERE user NOT IN (SELECT user FROM table WHERE project = $project) 

I just added a GROUP BY user at the end because one user can have more than one project.

[Piranha]

By the way, it would be better to use the user table. If you don't then you won't get the users that are not assigned to any project.

[nunomira]

Good catch!
I completely forgot about that possibility.

Thanks again.

I ended up with this:

SELECT users.id AS id, name AS data
FROM users
WHERE users.id NOT
IN (
	SELECT id_user
	FROM projects
	WHERE id_project = '$id_project'
)
AND active = 1
GROUP BY users.id
ORDER BY name ASC

[bradgrafelman]

Don't forget to mark this thread resolved.