CODEWITHSUNDEEP
mysqlsql
Gyandeep Sharma
Gyandeep Sharma

Reputation: 2327

SELECT query return error on Empty result

I'm using SELECT query as a subquery in Query given below...

SELECT COUNT (`Notification`.`id`) AS `count`
FROM `listaren_mercury_live`.`notifications` AS `Notification`
WHERE NOT FIND_IN_SET(`Notification`.`id`,(SELECT read_ids FROM read_notifications WHERE user_id = 46))
AND((notisfication_for IN("all","India"))
OR(FIND_IN_SET(46,notification_for))

This query working fine when

SELECT read_ids FROM read_notifications WHERE user_id = userid)

return result like 21,22,23,24

but when this query return empty.

The result id 0 instead of 3. There are 3 unread notifications for the user; so the result must be 3

Problem

The query gives correct result when internal select query's return like

12,42

but if query return null then the whole query's result becomes 0.

Expected Result With Query

Expected Result

Result I'm getting

Result I'm getting

I just want if the subquery returns an empty value. Then the query (Notification.id,(SELECT read_ids FROM read_notifications WHERE user_id) = 46) result looks like

 (`Notification`.`id`,(0))

instead of

(`Notification`.`id`,())

So it will work properly

Please tell me what improvement I need in this.

Thanks

Upvotes: 1

Views: 1811

Answers (1)

Nick
Nick

Reputation: 147196

Try replacing

FIND_IN_SET(`Notification`.`id`,(SELECT read_ids FROM read_notifications WHERE user_id = 46))

with

FIND_IN_SET(`Notification`.`id`,IFNULL((SELECT read_ids FROM read_notifications WHERE user_id = 46), ''))

From the manual, if the subquery result is empty it will return NULL, and FIND_IN_SET will return NULL if either argument is NULL, and NOT NULL is still NULL, as is NULL AND 1, and NULL is equivalent to false. So when the subquery returns NULL, your WHERE condition will fail.

By adding an IFNULL around the subquery result, you can get it to return a value which is valid for FIND_IN_SET, which will allow your query to work as expected.

Upvotes: 1

Related Questions