How to rank the most useful comments from a table?

Question

A following scenario:

Each Product has a comment, each comment will receive a like to inform if it was useful (it is optional to inform if you liked the comment)

I have a table called Customerreview and a table called likeReview and within this table I have the column isLike(true, false or null values). I need to sort this table such that comments with more positive Ikes are at the top of the list.

How can I do that?

My SQL code gives error when I try to sort by isLike, because there will be comments from the Customerreview table that will not be associated with the Likereview table

SQL code:

SELECT {c.pk}FROM { CustomerReview c LEFT JOIN     LikeReview l     ON {c.pk  "} = {l.pk} }WHERE {c.product} = ?product AND      {c.LANGUAGE  } = ?languageGROUP BY {c.pk}ORDER BY SUM(CASE WHEN {l.ISLIKE } = "true" THEN 1 ELSE 0 END) DESC

Answer 1

From what I understand of your explanation, for each record in Customerreview I may have one or more records in the Likereview table, some of which may be marked with the islike field as true.

To bring the customerReview with more amount of Likes marked as true, you could do so:

SELECT {c.pk}FROM { CustomerReview c 
LEFT JOIN     
LikeReview l     
ON {c.pk  "} = {l.fk} }
WHERE {c.product} = ?product 
AND      {c.LANGUAGE  } = ?language
AND {l.ISLIKE } = "true"
GROUP BY {c.pk}
ORDER BY COUNT({l.pk}) DESC