CODEWITHSUNDEEP
mysqlsql
R Hamilton
R Hamilton

Reputation: 263

SQL - order by is breaking my query when there is no reviews

I have the rather lengthy SQL query that I have included below. As you can see it orders by AvgRating and NumReviews, both of which rely on data from the reviews table. Unfortunately I need to see the rows in my results even when there are no reviews, currently if there are no reviews to order by then that row just doesnt show up in the results. All help greatly appreciated.

SELECT travisor_tradesperson.name, travisor_tradesperson.id, travisor_catagory.catname, 
                    travisor_company.cname, travisor_company.description, travisor_company.city, travisor_company.address, travisor_company.postcode, travisor_company.phone, 
                    ROUND(AVG(travisor_review.rating)) as RoundAvgRating, AVG(travisor_review.rating) as AvgRating, COUNT(travisor_review.rating) as NumReviews
            FROM `travisor_tradesperson`
            INNER JOIN travisor_company 
            ON travisor_tradesperson.company = travisor_company.id
            INNER JOIN travisor_catagory 
            ON travisor_tradesperson.catagory = travisor_catagory.id        
            INNER JOIN travisor_review 
            ON travisor_review.tradesperson = travisor_tradesperson.id 
            WHERE travisor_catagory.catname = '$catagory'
            AND travisor_company.city = '$city'
            GROUP BY travisor_tradesperson.name, travisor_catagory.catname, travisor_company.cname, 
                travisor_company.description
            ORDER BY AvgRating DESC, NumReviews DESC

Upvotes: 0

Views: 32

Answers (1)

dfundako
dfundako

Reputation: 8324

Left join travisor_review instead of Inner Join. Inner join will only find records that are present in both tables. If you have no reviews for that tradesperson record, it will drop from the results set.

Left join will return a NULL if it cannot match on the join predicate. In this case, the tradesperson will return but with a NULL. Convert the NULL to a 0 if needed and that should fix your AVG.

Upvotes: 3

Related Questions