SQL query that returns active users with no headset

April 10, 2023

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

>Solution :

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;