SQL Help

[Crundy]

Hello,
Here's one for all you SQL wizards out there:

OK, I have 2 tables, one holds descriptions of products, and the other holds user ratings for those products (2 tables are linked by ID), e.g.

table 1: descriptions
id | name
------------------
1 | Test product1
2 | Test product2

table 2: user ratings
prodid | rating | comments
------------------------------------
1 | 1 | This product sucks
2 | 4 | Quite good
1 | 3 | Average I suppose

My question is this, if I get the average user rating for, say, product 1 (select avg(rating) from ratings where prodid=1), how can I then compare this with the other products. In other words, I want to select all the entries from description where the average user rating is higher.

Can anyone help? C:\DOS:>
C:\DOS:>RUN
RUN DOS RUN!!

 

[r937]

since mysql doesn't do subqueries, you can solve this with two queries, one after the other

first, get the average rating for the product in question --

[tt]select avg(rating) as avgrating
from ratings
where prodid=1[/tt]

make a note of the answer, and use this in the next query --

[tt]select descriptions.name
, avg(rating) as avgrating
from descriptions
inner
join ratings
on descriptions.id = ratings.prodid
group
by prodid
having avg(rating) > answer[/tt]

rudy

 

[Crundy]

I tried this one which seemed to work:

select description.name, avg(comments.rating)
from description, comments
where description.id = comments.id
group by description.name
having avg(comments.rating) > 2;

where 2 is the result of the last query.

Does this work, or am I getting inaccurate results? C:\DOS:>
C:\DOS:>RUN
RUN DOS RUN!!

 

[r937]

well, i dunno, the column/table names are different from what you originally posted

you tell me -- based on your data, did it work?


rudy

 

[Crundy]

sorry, replace description with descriptions and comments with ratings.

It seems to be working, but I'm not sure if I'm using it correctly. Is that an efficient way of doing it? Did I set the 'group by' clause correctly? C:\DOS:>
C:\DOS:>RUN
RUN DOS RUN!!

 

[r937]

yes, you are using it correctly

in situations like this, it helps to have a representative sample of test data, a couple dozen rows, so that you can run the query a few different ways and verify the results yourself with a calculator