Use Count in mysql with AND, OR or NOT condition with or without HAVING?

Question

Hi i have the table "users" and "jobs" with following data

USERS

+------+---------------+-----------+---------+---------------------+
| id   | first_name    | last_name | role_id | created             |
+------+---------------+-----------+---------+---------------------+
| 1026 | Administrator | Larvol    |       2 | 2014-07-25 22:28:21 |
|   20 | Worker        | Larvol    |       3 | 2014-07-24 20:14:18 |
|   22 | test          | user      |       3 | 2014-07-25 16:06:27 |
+------+---------------+-----------+---------+---------------------+

JOBS

+----+--------+---------+
| id | status | user_id |
+----+--------+---------+
|  1 |      3 |      20 |
|  2 |      4 |      22 |
+----+--------+---------+

what i have done so far to fetch data from tables is

SELECT Worker.id, 
    first_name, 
    last_name, 
    role_id, 
    Worker.created, 
    (COUNT( NULLIF(Job.id, 0) )) AS  JobsAmount,
    ((SUM( IF( status <> 0, 1, 0 ) )-SUM( IF( status = 1, 1, 0 ) ))) AS  JobsReviewed 
FROM alpha_dev.users AS Worker LEFT JOIN jobs AS Job ON Job.user_id = Worker.id  
WHERE Worker.role_id = 3 
GROUP BY Worker.id;

and the result that i got is

+----+------------+-----------+---------+---------------------+------------+--------------+
| id | first_name | last_name | role_id | created             | JobsAmount | JobsReviewed |
+----+------------+-----------+---------+---------------------+------------+--------------+
| 20 | Worker     | Larvol    |       3 | 2014-07-24 20:14:18 |          1 |            1 |
| 22 | test       | user      |       3 | 2014-07-25 16:06:27 |          1 |            1 |
+----+------------+-----------+---------+---------------------+------------+--------------+

now i want to create a OR condition on "(COUNT( NULLIF(Job.id, 0) )) AS JobsAmount," something like

WHERE Worker.role_id = 3 OR (COUNT( NULLIF(Job.id, 0) )) = 1

but that was not working so instead i ended up with HAVING clause

WHERE Worker.role_id = 3 
GROUP BY Worker.id
HAVING COUNT(NULLIF(`Job`.`id`, 0)) = 0;

HAVING here works as a AND condition. and given me EMPTY SET, whereas i want condition in HAVING to work as a OR condition so either

Worker.role_id = 3 OR COUNT(NULLIF(`Job`.`id`, 0)) = 0

should be true to get things done.

Any help would be appreciated. Thanks

Answer 1

I don't know whether it is right or not but i followed a trick. instead of writing condition in where clause. i wrote condition too with HAVING something like

SELECT  Worker.id, 
        first_name,
        last_name, 
        role_id, 
        Worker.created, 
        (COUNT( NULLIF(Job.id, 0) )) AS JobsAmount, 
        ((SUM(IF(status <> 0, 1, 0))-SUM(IF(status = 1, 1, 0)))) AS JobsReviewed 
FROM alpha_dev.users AS Worker 
LEFT JOIN jobs AS Job 
ON Job.user_id = Worker.id 
GROUP BY Worker.id 
HAVING role_id = 3 OR JobsAmount = 5;

and it worked fine... so all i want to ask "is there any consequences in structuring query like this" or any performance issue??

please provide your valuable feedback.....

Answer 2

The expression COUNT(NULLIF(Job.id, 0)) will be zero if there isn't an associated row in Job.

Another way to check that there isn't an associated row in Job is to see if a required column in Job is null. I say required column because an optional column may be null anyway, whereas a required column will only be null if there aren't any matches in the left-joined table.

Put more concretely, in you specific scenario I'm pretty sure COUNT(NULLIF(Job.id, 0)) is the same condition as Job.user_id IS NULL. Because the expression Job.user_id IS NULL isn't based on an aggregate, it can go into the WHERE instead of the HAVING:

• role=3 OR job count = 0

  SELECT Worker.id, 
    first_name, 
    last_name, 
    role_id, 
    Worker.created, 
    (COUNT( NULLIF(Job.id, 0) )) AS  JobsAmount,
    ((SUM( IF( status <> 0, 1, 0 ))-SUM( IF( status = 1, 1, 0 ) ))) AS JobsReviewed 
  FROM alpha_dev.users AS Worker
  LEFT JOIN jobs AS Job ON Job.user_id = Worker.id  
  WHERE Worker.role_id = 3 OR Job.user_id IS NULL
  GROUP BY Worker.id
  

This will only work for COUNT=0. If you need to check for COUNT=2 or COUNT=5 or whatever, you'll need to push your existing query into a subquery, then have the outer query apply the OR logic:

• role=3 OR job count = 5

  SELECT * FROM (
      SELECT Worker.id, 
        first_name, 
        last_name, 
        role_id, 
        Worker.created, 
        (COUNT( NULLIF(Job.id, 0) )) AS  JobsAmount,
        ((SUM(IF(status <> 0, 1, 0))-SUM( IF(status = 1, 1, 0)))) AS JobsReviewed 
      FROM alpha_dev.users AS Worker
      LEFT JOIN jobs AS Job ON Job.user_id = Worker.id  
      GROUP BY Worker.id
  ) WorkerSummary
  WHERE role_id = 3 OR JobsAmount = 5
  

Answer 3

COUNT is an aggregation function. So without grouping something it doesn't mean anything. If you want an OR for those conditions then a solution might be a union of two queries with on one side the query without the having and just the role_id = 3 condition and on the other side the query with the having COUNT condition and role_id not equal to 3.

SELECT Worker.id, 
    first_name, 
    last_name, 
    role_id, 
    Worker.created, 
    (COUNT( NULLIF(Job.id, 0) )) AS  JobsAmount,
    ((SUM( IF( status <> 0, 1, 0 ) )-SUM( IF( status = 1, 1, 0 ) ))) AS  JobsReviewed 
FROM alpha_dev.users AS Worker LEFT JOIN jobs AS Job ON Job.user_id = Worker.id  
WHERE Worker.role_id = 3 
GROUP BY Worker.id

UNION

SELECT Worker.id, 
    first_name, 
    last_name, 
    role_id, 
    Worker.created, 
    (COUNT( NULLIF(Job.id, 0) )) AS  JobsAmount,
    ((SUM( IF( status <> 0, 1, 0 ) )-SUM( IF( status = 1, 1, 0 ) ))) AS  JobsReviewed 
FROM alpha_dev.users AS Worker LEFT JOIN jobs AS Job ON Job.user_id = Worker.id  
WHERE Worker.role_id <> 3 
GROUP BY Worker.id
HAVING COUNT(NULLIF(`Job`.`id`, 0)) = 0;

It might be possible to use a UNION ALL instead of a UNION, which has a better performance, but that depends on whether or not you want or will have duplicate rows because of the union.