My query is:
SELECT business.name, AVG(rating.value) as total
FROM business
INNER JOIN rating
ON business.id = rating.business_id
INNER JOIN directory
ON business.id = directory.item_id
WHERE directory.directory_id = 13
AND directory.type = 'business'
AND rating.rating_id = '1'
GROUP BY business.id
ORDER BY total DESC
My problem is that I want to include any businesses even if they do not have any ratings associated with them?
You need to use LEFT JOIN instead of INNER JOIN:
SELECT business.name, AVG(rating.value) as total
FROM business
INNER JOIN directory
ON business.id = directory.item_id
LEFT JOIN rating
ON business.id = rating.business_id AND rating.rating_id = '1'
WHERE directory.directory_id = 13
AND directory.type = 'business'
GROUP BY business.id
ORDER BY total DESC
If there are no ratings, total will be NULL. You can deal with this in MySQL (if you want) like this:
IFNULL(AVG(rating.value), 'No ratings') as total
Also note you need to move the rating.rating_id = '1' to the JOIN conditions.
Do a LEFT JOIN instead of your INNER JOIN when joining the rating-table.
You just need to change...
INNER JOIN rating
to
LEFT JOIN rating
An INNER JOIN returns a row where there is a match in both tables.
change INNER JOIN to LEFT JOIN when joining businesses and ratings
You want to do a LEFT OUTER JOIN.
SELECT business.name, AVG(rating.value) as total
FROM business
LEFT OUTER JOIN rating
ON business.id = rating.business_id
INNER JOIN directory
ON business.id = directory.item_id
WHERE directory.directory_id = 13
AND directory.type = 'business'
AND rating.rating_id = '1'
GROUP BY business.id
ORDER BY total DESC
