Help with SQL join

I have 3 tables a) BOOKS (id,name) b) RATINGS (book id,rating,ip address) c) comments (comment_id,book id,comments,date)

I want to show开发者_开发技巧 BOOK NAME,RATING,Number of COMMENTS using left join.

The issue with my query is it shows different ratings if the number of comments changes.

For example if there is one comment for a book the data returned by sql is perfect but the moment more comments are added the rating appears incorrect.

SELECT
    book.id,
    book.name,SUM( rtng.rating ) As total_rating,
    COUNT(distinct cmt.comment_id ) AS comment_count
FROM books book
    LEFT JOIN ratings rtng ON rtng.book_id = book.id
    LEFT JOIN comments cmt ON cmt.book_id = book.id

This way, you can get ALL books regardless of any missing a rating or comment... The primary table is books then do left join to each comment and rating respectively.

select
      B.ID,
      B.Name,
      COALESCE( TR.Rating, 0 ) TotalRating,
      COALESCE( TR.AvgRating, 0 ) AvgRating,
      COALESCE( COM.CommentCnt, 0 ) TotalComents
   FROM
      Books B
         LEFT JOIN ( select R.Book_ID,
                            SUM( R.Rating ) Rating,
                            AVG( R.Rating ) AvgRating
                        FROM Ratings R
                        Group By R.Book_ID ) TR
            ON B.ID = TR.Book_ID

         LEFT JOIN ( select C.Book_ID, 
                            count(*) CommentCnt
                        FROM Comments C
                        Group by C.Book_ID ) COM
            ON B.ID = COM.Book_ID

This likely has to do with the fact that the relationship between the books table and the comments (and ratings) tables is "one to many". You're going to have to do at least two queries:

(pseudocode follows)

all_book_ids = SELECT book_id FROM books

foreach a_single_book in all_book_ids
    SELECT * FROM comments WHERE book_id = a_single_book
    SELECT * FROM ratings  WHERE book_id = a_single_book

How about grouping based in books ID and additionally computing the average rating?

SELECT book.id,
       book.name,
       SUM( rtng.rating )              AS total_rating
       AVG( rtng.rating )              AS averageRating,
       COUNT(distinct cmt.comment_id ) AS comment_count
  FROM books                           AS book
  LEFT JOIN ratings rtng 
         ON rtng.book_id = book.id
  LEFT JOIN comments cmt 
         ON cmt.book_id = book.id    
  GROUP BY book.id

one more

SELECT book.id,book.name,
rtng.total_rating,
cmt.comment_count
from books book 
LEFT JOIN 
(
select book.id,book.name, SUM( rtng.rating ) As total_rating
from books book
inner join ratings rtng 
ON rtng.book_id = book.id 
group by book.id,book.name
)rtng 
ON rtng.book_id = book.id 

LEFT JOIN 
(
select book.id,book.name, COUNT(*) AS comment_count 
from books book
    left join comments cmt 
    ON cmt.book_id = book.id
group by book.id,book.name
)cmt 
ON cmt.book_id = book.id