CODEWITHSUNDEEP
mysqlsql
Jerielle
Jerielle

Reputation: 7520

How to get the highest average in a rating system using mysql?

I am creating a sort in my product page and I implement a sort using ratings. My problem is I am getting a multiple row of same products.

Here's my query:

SELECT DISTINCT(p.product_id) AS product_id, p.model AS model, r.rating AS rating FROM jp_product AS p
LEFT JOIN jp_review AS r
ON(p.product_id = r.product_id)
LEFT JOIN jp_user AS u
ON(p.seller_id = u.user_id)
WHERE p.`status` = 1
AND u.`status` = 1
ORDER BY r.rating DESC;

It will result like this:

*************************** 1. row ***************************
product_id: 143
     model: Kagoshima Satsuma-age
    rating: 5
*************************** 2. row ***************************
product_id: 145
     model: Filter-in Bottle
    rating: 5
*************************** 3. row ***************************
product_id: 143
     model: Kagoshima Satsuma-age
    rating: 2
*************************** 4. row ***************************
product_id: 145
     model: Filter-in Bottle
    rating: 2
*************************** 5. row ***************************
product_id: 51
     model: 4901362107276
    rating: NULL
*************************** 6. row ***************************
product_id: 69
     model: a3
    rating: NULL

....

As you can see the product ID 143 and 145 are duplicated. Can you help me with this?

Upvotes: 1

Views: 166

Answers (2)

additionster
additionster

Reputation: 628

Use MAX(rating).

SELECT p.product_id AS product_id, p.model AS model, MAX(r.rating) AS      
rating
FROM jp_product AS p
LEFT JOIN jp_review AS r
    ON (p.product_id = r.product_id)
LEFT JOIN jp_user AS u
    ON (p.seller_id = u.user_id)
WHERE p.`status` = 1 AND u.`status` = 1
GROUP BY p.product_id, p.model
ORDER BY r.rating DESC;

Upvotes: 2

Zafar Malik
Zafar Malik

Reputation: 6854

You can try below query-

SELECT p.product_id AS product_id, p.model AS model, MAX(r.rating) AS rating 
FROM jp_product AS p
JOIN jp_review AS r ON p.product_id = r.product_id
JOIN jp_user AS u ON p.seller_id = u.user_id
WHERE p.`status` = 1 AND u.`status` = 1
GROUP BY p.product_id;

Note: As you are filtering data from jp_user also means you need only matching rows, so no need of left join simple join will work.

Also group by will make rows unique so no need of distinct.

Further if you want to sort based on product_id then apply order by product_id at the end.

Upvotes: 1

Related Questions