CODEWITHSUNDEEP
mysql
Orangeman555
Orangeman555

Reputation: 1181

Mysql If Empty Result Return Default Value

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?

Upvotes: 0

Views: 1112

Answers (1)

Fabricator
Fabricator

Reputation: 12782

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

Upvotes: 2

Related Questions