Trouble with multiple tables AVG() and COUNT()

[Albion]

I have three tables.

tblVenue
Venue_NUM (Primary Key)
VenueName

tblShow
Show_NUM (Primary Key)
Venue_NUM (Link to tblVenue)
BandName

tblRatings
Ratings_NUM (Primary Key)
Show_NUM (Link to tblShow)
Rating
IP_Address

I am trying to figure out how to write an SQL statement that would show:

tblVenue.VenueName, tblShow.BandName, 0, 4

where 0 is the count of ratings entered by the person at IP address 192.168.1.1 and 4 is the average of all the ratings for tblShow.BandName.

And I cannot write multiple SQL statements, this all has to be inside of one SQL statement. Is it possible?

Thanks

-Al

 

[JarlH]

select v.VenueName,
s.BandName,
sum(case IP_Address when '192.168.1.1' then 1
else 0 end),
avg(rating*1.0)
from tblVenue v,
tblShow s,
tblRatings r
where v.Venue_NUM = s.Venue_NUM and s.Show_NUM = r.Show_NUM
group by v.VenueName,s.BandName

 

[Albion]

This works, but it only shows those shows in which there are ratings. I need to have all the shows displayed including those which do not have ratings yet.

-Al

 

[rrrkrishnan]

select v.VenueName,
s.BandName,
sum(case IP_Address when '192.168.1.1' then 1
else 0 end),
avg(rating*1.0)
from tblVenue v join tblShow s on v.Venue_NUM = s.Venue_NUM
left outer join tblRatings r on r.Show_NUM = s.Show_NUM
group by v.VenueName,s.BandName