MYSQL: Stop IFNULL returning "empty" rows of null

Question

I'm using IFNULL in the following way:

SELECT name, address, birthday, IFNULL(GROUP_CONCAT(pet_name), 'none') AS `pets_names` FROM pets WHERE user_id = 1;

However, If I have no user 1, and therefore no results, I will get a single row where all fields are null apart from pet_names which will say 'none'. I tried setting it to only give a value if user_id IS NOT NULL but then I got a row with every value as NULL. How can I get it to just not return any rows when using IFNULL?

*If I have no user 1, and therefore no results, I will get a single row where all fields are null apart from pet_names which will say 'none'.* Impossible. You must receive empty set, without rows at all. If you see a row with nulls and 'none' then this is your client "service". — Akina, Sep 17 '20 at 11:33
Does this answer your question? [SQL: How to disable result of aggregate on empty table?](https://stackoverflow.com/questions/52514329/sql-how-to-disable-result-of-aggregate-on-empty-table) — Solarflare, Sep 17 '20 at 12:29

score 0 · Accepted Answer · answered Sep 17 '20 at 16:09

0

I've fixed this by adding

GROUP BY name

I presume because there are no names, there's nothing to group by and thus no results.

answered Sep 17 '20 at 16:09

CryLittleSister

35
7

MYSQL: Stop IFNULL returning "empty" rows of null

1 Answers1