Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SELECT average values FROM another table
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

smcallister
Starting Member

15 Posts

Posted - 05/19/2008 :  15:18:35  Show Profile  Visit smcallister's Homepage  Reply with Quote
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

Sweden
30421 Posts

Posted - 05/19/2008 :  15:21:35  Show Profile  Visit SwePeso's Homepage  Reply with Quote
select l.id, l.listid
from listings as l
left join (select listid, avg(rating) as Yak from ratings group by listid) as r on r.listid = l.listid
order by r.yak



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

smcallister
Starting Member

15 Posts

Posted - 05/19/2008 :  15:45:44  Show Profile  Visit smcallister's Homepage  Reply with Quote
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_LISTINGS
column: ID
column: name
column: style

TABLE_REVIEWS
column: listingID
column: rating


I 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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 05/19/2008 :  15:53:35  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Here it is s-p-e-l-l-e-d o-u-t for you.

select l.id, l.name
from table_listings as l
left join (select listingid, avg(rating) as Yak from table_reviews group by listingid) as r on r.listingid = l.id
where l.style = 2
order by r.yak



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

smcallister
Starting Member

15 Posts

Posted - 05/19/2008 :  16:02:56  Show Profile  Visit smcallister's Homepage  Reply with Quote
THANKS!!!! I GOT IT WORKING!!!

What should I use to call the value of "Yak" for each listing? I'm using PHP
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 05/20/2008 :  02:07:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
select		l.id,
		l.name,
		r.Yak AS MyAlias
from		table_listings as l
left join	(
			select		listingid,
					avg(rating) as Yak
			from		table_reviews
			group by	listingid
		) as r on r.listingid = l.id
where		l.style = 2
order by	r.yak



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

smcallister
Starting Member

15 Posts

Posted - 05/20/2008 :  09:57:52  Show Profile  Visit smcallister's Homepage  Reply with Quote
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!!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000