Question 20137 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL query that returns active users with no headset – Mysql

I have 4 tables:

Table user with id, email, first_name, last_name, country, activated
Table assets with id, serial, model_id, assigned_to
Table models with id, name, category_id
Table categories with id, name
Table status_labels with id, name

& I want to know active people that have no headset, I tested a query but returns me nothing, because a user can have a laptop or desktop and monitor, etc. which means assets should not be null

Here is my query:

SELECT u.id, u.email, u.first_name, u.last_name
FROM users u
WHERE u.activated = 1 AND u.country = 'MA' AND u.id NOT IN (
    SELECT assigned_to
    FROM assets a
    INNER JOIN models m ON a.model_id = m.id
    INNER JOIN categories c ON m.category_id = c.id
    WHERE c.name = 'Desktop'
)

Here is another one: it returns also nothing

SELECT u.id, u.email, u.first_name, u.last_name
FROM users u
LEFT JOIN assets a ON a.assigned_to = u.id
LEFT JOIN models m ON m.id = a.model_id
LEFT JOIN categories c ON c.id = m.category_id
WHERE u.activated = 1 AND u.country = 'MA' AND c.name = 'Headset' AND a.id IS NULL;

I’m expecting values in return

Tags:

2

Answers


  1. 0

    You could aggregate by user and then assert that the headset count be zero:

    SELECT u.id, u.email, u.first_name, u.last_name
FROM users u
LEFT JOIN assets a ON a.assigned_to = u.id
LEFT JOIN models m ON m.id = a.model_id
LEFT JOIN categories c ON c.id = m.category_id
WHERE u.activated = 1 AND u.country = 'MA'
GROUP BY u.id, u.email, u.first_name, u.last_name
HAVING SUM(c.name = 'Headset') = 0;
    Login or Signup to reply.
  2. 0

    The condition c.name = 'Headset' should be moved to the ON clause so that the users without headsets are not filtered out and the condition a.id IS NULL should be changed to c.id IS NULL because the table categories is the one you should check for a non-match:

    SELECT u.id, u.email, u.first_name, u.last_name
FROM users u
LEFT JOIN assets a ON a.assigned_to = u.id
LEFT JOIN models m ON m.id = a.model_id
LEFT JOIN categories c ON c.id = m.category_id AND c.name = 'Headset'
WHERE u.activated = 1 AND u.country = 'MA' AND c.id IS NULL;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search