Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
smcallister
Starting Member
15 Posts |
Posted - 2008-05-19 : 15:18:35
|
So I have this table called "listings"... there are 100 unique listings with an integer ID for each.I have another table called "ratings"... in there are multiple entries that have a listing_id field and a rating field. The rating field is a value from 0-10.I want to select ALL "listings" from the listings table... and then sort based on the average number that the multiple rating fields in the ratings table has for that listing.I CAN NOT figure it out!! Any help would be greatly appreciated. Please respond if I have not explained this properly. Thanks in advance. |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-19 : 15:21:35
|
select l.id, l.listidfrom listings as lleft join (select listid, avg(rating) as Yak from ratings group by listid) as r on r.listid = l.listidorder by r.yak E 12°55'05.25"N 56°04'39.16" |
|
|
smcallister
Starting Member
15 Posts |
Posted - 2008-05-19 : 15:45:44
|
OK, this is putting me on the right track but I can't seem to convert it into what I need. I will give some more specifics about my table.TABLE_LISTINGScolumn: IDcolumn: namecolumn: styleTABLE_REVIEWScolumn: listingIDcolumn: ratingI want to SELECT * from TABLE_LISTINGS where STYLE = 2.Then, for each ID in TABLE LISTINGS, I want to SELECT * from TABLE_REVIEWS where listingID matches ID.. and I want to take the average rating of the returned columns.Then I want to SORT the TABLE_LISTINGS based on the results of those averages from TABLE_REVIEWS.Thanks. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-19 : 15:53:35
|
Here it is s-p-e-l-l-e-d o-u-t for you.select l.id, l.namefrom table_listings as lleft join (select listingid, avg(rating) as Yak from table_reviews group by listingid) as r on r.listingid = l.idwhere l.style = 2order by r.yak E 12°55'05.25"N 56°04'39.16" |
|
|
smcallister
Starting Member
15 Posts |
Posted - 2008-05-19 : 16:02:56
|
THANKS!!!! I GOT IT WORKING!!!What should I use to call the value of "Yak" for each listing? I'm using PHP |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-20 : 02:07:44
|
[code]select l.id, l.name, r.Yak AS MyAliasfrom table_listings as lleft join ( select listingid, avg(rating) as Yak from table_reviews group by listingid ) as r on r.listingid = l.idwhere l.style = 2order by r.yak[/code] E 12°55'05.25"N 56°04'39.16" |
|
|
smcallister
Starting Member
15 Posts |
Posted - 2008-05-20 : 09:57:52
|
peso, thank you very much!! you have not only fixed this problem, but have also progressed my understanding of SQL into a whole new avenue. I have thanked you in my code and when my little project is complete, I will forward you the link!! Much appreciated!!! |
|
|
|
|
|
|
|