I wanted to generate a report consists of per author total earning.
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_earning) 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 sum of the total earning is actually 2863.22. But if i add all the values of the result table I get 2415. Where is the problem? What I am missing? I am sure SQL Gurus here can help me out.
Hmmm. Indirectly I suppose you could say JOINs have something with Database Designing, in the sense that when you design your database you need to find out which tables you need and what the relations between those tables are and from there you know which tables to JOIN when.
But it’s not a very direct relation.
I really suggest you read some more tutorials and/or a book as JOINs are not something that can be easily explained through forum posts
Thanks. It is working great. Thanks again. It was very confusing. Now it got cleared. I will go through “Simply SQL”. I am also very much confused about SQL Joins. I read many free tutorials available but couldn’t understand the basic yet. Does it have anything to do with Database Designing ?
Ah yes, I should have put parentheses around the subqueries:
SELECT
author_id
, post_id
FROM
tableB
WHERE
author_id NOT IN (
SELECT
author_id
FROM
tableA
)
SELECT
post_id
, post_earning
FROM
tableC
WHERE
post_id NOT IN (
SELECT
post_id
FROM
tableB
)
Where is that LIMIT 0,30 coming from by the way, are you running the queries through PHPMyAdmin
Also, are your tables really called tableA, tableB and tableC ?
If not, replace those names in the queries above with the actual names.
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘SELECT post_id FROM tableB LIMIT 0, 30’ at line 8
Wow, I’m using the leading comma convention and still I manage to mess up the commas
Here ya go:
SELECT
a.author_id
, a.author_name
, sum(post_earning) 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
UNION ALL
SELECT
-1
, "Others"
, sum(post_earning) as total_earning
FROM
tableC
WHERE
post_id NOT IN (
SELECT
post_id
FROM
tableB
)
If you want to get a basic knowledge on MySQL google for “MySQL tutorial” or get a book, like Simply SQL from Rudy Limebeck
SELECT SUM( post_earnings )
FROM TableC
WHERE post_id NOT
IN (
SELECT post_id
FROM TableB
)
returned the rest of the amount i was searching. Thanks man. I will be glad if you give me some noob reference on mysql as i can develop my mysql skill to a minimum level.
The first way would include a UNION ALL in the SELECT query and would look something like this:
SELECT
a.author_id,
a.author_name,
sum(post_earning) 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
UNION ALL
SELECT
-1,
, "Others"
, sum(post_earning) as total_earning
FROM
tableC
WHERE
post_id NOT IN (
SELECT
post_id
FROM
tableB
)
got an error on this one
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' "Others" , sum(post_earning) as total_earning FROM tableC WHERE post_id ' at line 14
There is no absolute correct way to do it, there is only the way you want to do it.
You could either rewrite the query such a way that there is a new row with a name “Others”, or you could make sure that when you delete a row from tableB, the corresponding rows in tableC are removed as well.
That also holds for tableA. If you remove an author from there, you should remove the posts from that author from tableB, and the corresponding earnings from tableC.
The first way would include a UNION ALL in the SELECT query and would look something like this:
SELECT
a.author_id,
a.author_name,
sum(post_earning) 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
UNION ALL
SELECT
-1,
, "Others"
, sum(post_earning) as total_earning
FROM
tableC
WHERE
post_id NOT IN (
SELECT
post_id
FROM
tableB
)
For the second way, take a look at foreign keys with ON DELETE CASCADE.
That only works if your tables are InnoDB. If they are MyISAM you should add this functionality to your application.
I believe some of the posts were deleted from the post table and they are 59 in total. in that case the earning will not be given to any author and can be stored as others. I am a noob in database design and sql queries. So Please advice.
What that tells you is that there are records in tableC for which there are no records in tableB
In other words, you either inserted rows in tableC with a post_id that didn’t exist in tableB, or you removed posts from tableB without removing the corresponding rows from tableC.
You need to figure out which of these two is the case and make sure it doesn’t happen again. Adding a foreign key on tableC helps (after you’ve solved the problem).
