mysql left join respect condition of all row joined

Question

i have two table, fos_user and zone with

fos_user { id, name, enabled}
zone {id, fos_user_id, enabled}

for each fos_user can exists more than zone

i need to filter fos_users that have ONLY zone with enabled=1

i try with

SELECT * FROM fos_user as u
LEFT JOIN zona as z ON u.id=z.users_id
WHERE z.enabled=1 AND  u.enabled=1

but i get a list of fos_user that have almost one zone with enabled=1

how i can fix?

thank you

What if a user has no `zone` records? – shmosel Mar 03 '17 at 01:52 — shmosel, Mar 03 '17 at 01:52

score 3 · Answer 1 · answered Mar 03 '17 at 01:54

3

You can group by user and compare the number of enabled zones against the total number of zones:

SELECT *
  FROM fos_user u
  JOIN zone z ON u.id = z.users_id
 WHERE u.enabled = 1
 GROUP BY u.id
HAVING SUM(z.enabled = 1) = COUNT(*);

answered Mar 03 '17 at 01:54

shmosel

49,289
6
73
138

If user has no `zone` records and you want to include it as valid `SUM` record, the `z.enabled` value would be `NULL`, to make it count just do: `HAVING SUM(IFNULL(z.enabled = 1, 1)) = COUNT(*)` – Litzer May 26 '22 at 09:20

score 1 · Answer 2 · edited May 23 '17 at 12:32

You need a second condition to exclude users in non-enabled zones.

SELECT *
FROM fos_user AS u
INNER JOIN zone AS z on u.id = z.users_id
LEFT JOIN zone AS z1 on u.id = z1.users_id AND z1.enabled = 0
WHERE u.enabled = 1 AND z.enabled = 1
AND z1.id IS NULL

You use INNER JOIN to match zones exactly, and you use LEFT JOIN with a NULL test to find users that don't match that criteria (see Return row only if value doesn't exist).

mysql left join respect condition of all row joined

2 Answers2