CODEWITHSUNDEEP
mysql
Sean Kimball
Sean Kimball

Reputation: 4494

MySQL IFNULL not returning 0 on table with no matching records as expected

I'm trying to count the number of searches a user performed using:

SELECT IFNULL(COUNT(`searches`.`internalKey`),0) AS `activity`, `modUser`.`username`, `modUser`.`active`, `modUser`.`id` AS `customer_id`,`UserGroupMembers`.`user_group`, `Profile`.*, `extdata`.*
FROM `modx_users` AS `modUser`
LEFT JOIN `modx_user_attributes` `Profile` ON `modUser`.`id` = `Profile`.`internalKey`
LEFT JOIN `modx_member_groups` `UserGroupMembers` ON `modUser`.`id` = `UserGroupMembers`.`member`
LEFT JOIN `modx_gssi_user_ext_data` `extdata` ON `extdata`.`internalKey` = `modUser`.`id`
LEFT JOIN `modx_gssi_user_queries` `searches` ON `searches`.`internalKey` = `modUser`.`id`
WHERE (`UserGroupMembers`.`user_group` IN (2))
GROUP BY `searches`.`internalKey`
ORDER BY `Profile`.`fullname`;

If the user [identified by their internaKEy] has not performed any searches, there are no records in the modx_gssi_user_queries table & should be NULL, but the IFNULL statement does not seem to be working as I expect it to.

What am I doing wrong here.

Upvotes: 0

Views: 50

Answers (1)

Barmar
Barmar

Reputation: 781058

Don't GROUP BY the column from the table that may have no matches, because that will combine all the users that have no searches into one group. Use GROUP BY modUser.id.

Upvotes: 1

Related Questions