Page 1 of 1

Should be a simple query -- but i'm no SQL guru :)

Posted: Tue Jul 28, 2009 2:46 pm
by alex.barylski
I have two tables:

Code: Select all

system_ratings:
pkid, name, value
 
product_ratings:
pkid, rating_id, user_id, product_id, date, rating
I need to pull all the records for system_ratings (in a paginated fashion 5 at a time) and generate list of rating options BUT I would ideally like them to use the counts in product_ratings for a given product_id

For instance with no product ratings (product_ratings records = 0) system_ratings would return the entire resultset ordered alphanumerically.

However if three people rated product ID = 3 four times so the table looked like:

Code: Select all

 
1, 1, 1, 1, 0, 4.4
2, 2, 1, 1, 0, 5.0
3, 3, 1, 1, 0, 2.0
4, 2, 2, 1, 0, 5.0
 
two people rated the product, but one person rated on three items, whereas another only rated once.

This would affect the default alphanumeric ordering to shift in favour of whatever rating_id = 2 would be as it had the most 'votes' -- make sense?

I am not overly fluent in SQL for anyting beyond a simple query so I'm not even sure where to start.

I assume I have to GROUP BY or similar but again I have no idea how to even start, every query I do have limits the results only to those that match exclusively, that is, an entry in one table exists in the other, when in reality what I need is the result set to list all items in system_ratings, but have the order be influenced/changed according to the number of items are found to relate back to the system_ratings table.

Cheers,
Alex

Re: Should be a simple query -- but i'm no SQL guru :)

Posted: Tue Jul 28, 2009 2:57 pm
by Eran
make sense?
Actually, no. I wanted to have a go at your query, but I couldn't decipher your problem at all... what is the relationship between the system_ratings and product_ratings? what does the rating_id field in the product_ratings stand for (it already has an ID field)? the schema structure is not very obvious
You said the data you showed was for three people who rated a product with an ID = 3, but looking at it there is '1' where I would think the product_id should be (it would have helped if you put the field names above the data). And there are only four rows - are you aggregating the ratings? why would you need a separate ID for this table if you are doing so (just use product_id as the primary key)? and are you not keeping count of how many votes were placed?

I would appreciate it if you could explain again your problem, and the result query you want to achieve.

Re: Should be a simple query -- but i'm no SQL guru :)

Posted: Tue Jul 28, 2009 4:51 pm
by alex.barylski
I managed to get our dba to hammer out a solution...ikll show u tomorrow when I go back to work :)