Reputation: 7520
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
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
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