SELECT query return error on Empty result

Question

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

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

You really need to show us sample data from your tables. As a general comment, you should **not** be storing CSV data in your table. So the real issue is that, whether right or wrong, your data design is wrong. — Tim Biegeleisen, Apr 09 '18 at 11:04
Fix your data model. Storing lists of numbers as a comma-delimited string is simply wrong in a SQL database. — Gordon Linoff, Apr 09 '18 at 11:05
iam totally with the other comment's here is more info on why you shouldn't store CSV into a database -> https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — Raymond Nijland, Apr 09 '18 at 11:11
look into MySQL's control flow functions -> https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html — Raymond Nijland, Apr 09 '18 at 11:20

score 1 · Accepted Answer · answered Apr 09 '18 at 12:37

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.

SELECT query return error on Empty result

1 Answers1