user2402107 user2402107 - 2 months ago 9
SQL Question

Sql Join between two tables with a group by

I am extremely new to SQL and I have been working on this problem for hours and I am so close but not quite there:

Three tables

Category



cid
cname

Product

pid
cid
pname
brand
price


Review

rid
userid
pid
rdate
score rcomment


I am trying to


  • Return the product name and average score.

  • Return the names of product under the category TV and with average rating above 4.0



1:

select avg(score), review.pid
from review
join product
on review.pid = product.pid
group by review.pid;


2:

select * from product
join review
on product.pid = review.pid
where cid ='1';


Here is a fiddle:
http://sqlfiddle.com/#!4/a6b30/1

Answer
  1. You can add the pname to your GROUP BY clause and then to your SELECT clause. pid is unique for each product so it does not affecting the query at all, but let you add the name to your results.

    select avg(review.score), product.pname
    from review
    join product
    on review.pid = product.pid
    group by product.pname;
    
  2. In the following query I use a subquery to select only items with average score >= 4 using HAVING clause. Then I select only pid that appear in the subquery's result and adding the cid='1' part.

    select product.pname
    from product
    join review
    on product.pid = review.pid
    where cid ='1'
    and pid IN (SELECT pid FROM review GROUP BY pid HAVING AVG(score) >= 4);