Re: How to Join a table with Other (result) Tables ? Complex one !

Try something like this:

select
 D.docID, D.docName, COALESCE(AVG(rating),0) AS avgRating
from tbl_documents as D
left outer join tbl_Rating as R
on R.docID = D.docID
group by D.docID, D.docName

If you need the average to show 3.333333 instead of 3, use this
expression for avgRating:  COALESCE(AVG(1.0*rating),0.0)

Steve Kass
Drew University

velu wrote:

Here is the situation 

Table 1 : tbl_documents

docID	docName
1	aaa
2	bbb
3	ccc

Table 2 : tbl_Rating

ratID	rating	docID
1	3	1
2	5	1
3	2	1
4	3	2

The queary I need is to display the result in this form. must be like this

docID	docName 	Avaragerating
1	aaa		3
2	bbb		3
3	ccc		0

NOTE : For getting the average I used this queary “SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount FROM tbl_Rating WHERE tbl_rating.docID = tbl_documents.docID” 

PLs help me ?

Thx


.