-1

I have a hard time to figure out how I can pull up a list of users who are not in the list of another mysql table.

Example : I have a list of all the users in my users table. My users table represents a list of all the employees in the company.

I have another table with the list of all the devices (laptop, iphone etc..). each device are assigned to a user.

I would like to know based on those 2 tables. The list of the users who does not have their name on the devices table ?

Is there a way to do this in PHP / MYSQL ?

I have a hard time to figure out how I can pull up a list of those users who does not appear at all on the device table ?

Jean D
  • 11
  • 3

2 Answers2

0

You can left join the table and find the null value on the device table. Example: "user" table with user_id, user_name "device" table with device_id, device_name, user_id

If you want to find out which users do not have a device, use this query:

select user_id, user_name
from user u 
left join device d on u.user_id=d.user_id
where d.user_id is null

This should show you all the users who does not have a corresponding record in the device table.

Tracy Tan
  • 1
  • 1
0

Yet another option is to exclude from your set of users the ones with devices using a NOT IN operator inside the WHERE clause.

SELECT *
FROM user
WHERE id NOT IN (SELECT id FROM device)
lemon
  • 14,875
  • 6
  • 18
  • 38