Left Join In Query

[betheball]

Smarter than I used to be, but not smart enough yet.

I have two tables. One lists videos. The second contains people's ratings of those videos. I need a query that will display all videos and their average rating. I managed to get one that almost works. My snag came because some videos have no ratings yet and as a result are excluded from the recordset. Any ideas on how I can return all videos those with and without ratings. Here is the code that I used to get me as far as I am:

SELECT Video.NameID, Video.Name, Avg(Ratings.Rating) AS AvgRating FROM Video INNER JOIN Ratings ON Video.ID = Ratings.ID WHERE Video.NameID>100 GROUP BY Video.ID, Video.Name

 

[craw]

you can give this a try

i assume that both tables have some sort of primary key (movie title or movieID number)... you could make a query from the two tables and join on this unique field. then change the join properties to give you all the movies from the video table and those only those from the ratings table where the two are equal. you need to include the PK from each table and for the PK in the ratings table, put Is Null in the criteria. i'm pretty sure that's how you can do it...

 

[betheball]

Re: you can give this a try

i assume that both tables have some sort of primary key (movie title or movieID number)... you could make a query from the two tables and join on this unique field. then change the join properties to give you all the movies from the video table and those only those from the ratings table where the two are equal. you need to include the PK from each table and for the PK in the ratings table, put Is Null in the criteria. i'm pretty sure that's how you can do it...

Thanks Craw. Both tables have a primary key, but I can't join on them. The primary key in the video table is ID. There is a primary key in the Reviews table titled ID too, but the two don't relate. The Reviews table also has a field titled VideoID that contains the same value as the ID field from the Video table, which is why I am joining on those two fields. I actually mistyped the join portion of my SQL above it is really:

INNER JOIN Ratings ON Video.ID = Ratings.VideoID

 

[Pat Hartman]

Changing INNER to LEFT in your Join may get the result you are looking for. That would return a row for each video even if there is no rating for it.

 

[betheball]

Exactly! Thanks Pat.