Hi all,
Wondering is this possible:
SELECT id AS cid, name, phone, url, streettype, IFNULL((SELECT ROUND(AVG(stars)*2, 1) / 2 FROM " . $dbconf{'prefix'} . "reviews WHERE location=cid) AS avgstars,0), ...
OR should it be:
SELECT id AS cid, name, phone, url, streettype, (SELECT ROUND(AVG(stars)*2, 1) / 2 FROM " . $dbconf{'prefix'} . "reviews WHERE location=cid) AS IFNULL(avgstars,0), ...
I am fairly sure you can see what I am trying to achieve here.
In other words, two tables, one with id's and another that relates those ID's to star ratings.
I want to extract the AVG(stars) rating AS avgstars to operate on it later, but, if there is no results for the query: ie. no row exists with the "location" specified, can I make the return "avgstars" equal zero rather than simply NULL.
Thanks,
Aaron.