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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

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

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post 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.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

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

Post by alex.barylski »

I managed to get our dba to hammer out a solution...ikll show u tomorrow when I go back to work :)
Post Reply