CODEWITHSUNDEEP
mysqlsql
skamsie
skamsie

Reputation: 2726

SQL query with having count with condition

I would like to retreive records based on a joined table's number of associations with a condition. In plain English the query would be:

Get all properties where the number of associated logs having action one of ('foo', 'bar', 'moo') is less than 5 (can also be NULL). So to be more exact, it can have 10 logs, but if 4 or more of them have action one of ('foo', `'bar', 'moo') then it should not be included.

Without the count condition is working, even though I am not sure it includes the ones with no logs:

SELECT p.id
     , count(l.object_id) 
  FROM properties p
  LEFT 
  JOIN logs l
    ON l.object_id = p.id
   AND l.object_type = 'Property'
 GROUP 
    BY p.id 
 HAVING (count(l.object_id) < 5);

The problem is if I want the condition specified above, it does not work (syntax is obviously wrong).

SELECT properties.id, FROM `properties` LEFT OUTER JOIN `logs` ON
    -> `logs`.`object_id` = `properties`.`id` AND `logs`.`object_type` = 'Property'
    -> GROUP BY properties.id HAVING (count(logs.object_id where logs.action in
    -> ('foo', 'bar', 'moo')) < 5);

NOTE: The logs table has over 10 milion records... I have tried with a nested select where, but it is not an option as the query times out.

Upvotes: 1

Views: 583

Answers (1)

forpas
forpas

Reputation: 164099

In MySql the condition can be written as:

HAVING SUM(logs.action in ('foo', 'bar', 'moo')) < 5

In case there are no matches from the the table logs the above sum will return null, do if you want these rows returned use COALESCE():

HAVING COALESCE(SUM(logs.action in ('foo', 'bar', 'moo')), 0) < 5

Upvotes: 1

Related Questions