Mysql If Empty Result Return Default Value

Question

I'm attempting to make mysql return a basic set of values if initial entries do not exist.

This is what I've attempted:

SELECT 
IFNULL(count( mcr.rating ), 1) as rating_count,
IFNULL(avg( mcr.rating ), 5) as rating_average,
IFNULL(sum( mcr.rating  ), 5) as rating_value,

IFNULL(mck.slug, '-') as rating_slug,
IFNULL(mck.name, '-') as rating_name,
IFNULL(mck.criteria_id, '-') as id,
IFNULL(mck.category, '-') as category

FROM microstock_criteria_key AS mck
LEFT JOIN microstock_collective_reviews AS mcr ON mck.criteria_id = mcr.criteria_id
WHERE mcr.agency_id =5
GROUP BY mck.criteria_id
ORDER BY mck.priority ASC

Yet I still get:

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0004 sec)

When I should (if I understand) be getting the defaults I set in the IFNULL statements.

So how can this be done properly to return default results upon would-be empty results?

If there are absolutely no results from mck, then you will need to UNION with a default selector. — user1932079
– user1932079, Commented Jun 7, 2014 at 7:08

Fabricator · Accepted Answer · 2014-06-07 07:10:32Z

2

Since you are using the review table's agency_id to filter, I think you want a RIGHT JOIN.

SELECT 
IFNULL(count( mcr.rating ), 1) as rating_count,
IFNULL(avg( mcr.rating ), 5) as rating_average,
IFNULL(sum( mcr.rating  ), 5) as rating_value,

IFNULL(mck.slug, '-') as rating_slug,
IFNULL(mck.name, '-') as rating_name,
IFNULL(mck.criteria_id, '-') as id,
IFNULL(mck.category, '-') as category

FROM microstock_criteria_key AS mck
RIGHT JOIN microstock_collective_reviews AS mcr ON mck.criteria_id = mcr.criteria_id
WHERE mcr.agency_id =5
GROUP BY mck.criteria_id
ORDER BY mck.priority ASC

answered Jun 7, 2014 at 7:10

Fabricator

12.8k2 gold badges29 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql If Empty Result Return Default Value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related