I have 3 tables how to join them to generate a table?

Question

Table a (Author Table)

author_id
author_name

Table b (Post Table)

post_id
author_id

Table c (Earning Table)

post_id (post id is not unique)
post_earning

I wanted to generate a report consist of earnings per author.

author_id
author_name
total_earning (sum of earnings of all the posts by author)

The SQL Query used:

SELECT
   a.author_id,
   a.author_name,
   sum(post_earnings) as total_earnings
FROM TableA a
Inner Join TableB b on b.author_id = a.author_id
Inner Join TableC c on c.post_id = b.post_id
Group By 
   a.author_id,
   a.author_name

The Result I got is this :

ID  user_login  total_earnings
2   Redstar 13.99
7   Redleaf 980.18
10  topnhotnews 80.43
11  zmmishad    39.27
13  rashel  1248.34
14  coolsaint   1.66
16  hotnazmul   9.83
17  rubel   0.14
21  mahfuz1986  1.09
48  ripon   12.96
60  KHK 27.81

the sum of the total earning is actually 2863.22. But if i add all the value of the result table I get 2415. Where is the problem? The sample tables used can be downloaded from the links in first comment.

Answer 1

SELECT
   a.author_id,
   a.author_name,
   sum(post_earnings) as total_earnings
FROM TableA a
Inner Join TableB b on b.author_id = a.author_id
Inner Join TableC c on c.post_id = b.post_id
Group By 
   a.author_id,
   a.author_name

Answer 2

Are you sure that you have a right data in tables. Maybe a author is missing in TableA or some post in posts table.