mysql query help

Question

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?

Answer 1

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

Answer 2

Do a LEFT JOIN instead of your INNER JOIN when joining the rating-table.

Answer 3

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.

Answer 4

change INNER JOIN to LEFT JOIN when joining businesses and ratings

Answer 5

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.