rating system

[jeremy]

this is probably a simple sql solution, but i could use some help.
i've got one table that has rows of documents, and another table with
any number of ratings for each of the documents. The rating table is
linked with an id column. the issue i am running up against is
returning a single average rating for a document.

SELECT document.title, (SELECT AVG(scale)
FROM Rating, document
WHERE
document.pkDocumentId = rating.documentId)
FROM Document

returns the same avg for all the ratings.

So i'd ideally like to return the values of the document columns
(tableA.*), with an additional column containing the average rating of
that document (avg(tableB.Scale) where pkDocumentId = documentId).

thanks in advance.

[David Portas]

SELECT D.title, AVG(R.scale)
FROM Document AS D
JOIN Rating AS R
ON D.pkdocumentid = R.documentid
GROUP BY D.pkdocumentid, D.title

--
David Portas
SQL Server MVP
--