MySQL Select Statement add row for items that are not listed

Hi All,
We have a web application where people post their royalties to.  I have to create a report that will select all of the royalties and also show on the report the franchises that did not post their royalties

The database has simple table that collects the royalties.  I am able to pull the royalties but I do not know how to add an additional row for the franchises that did not post for the month and year

The table as 2 columns that are called month and year

For example:

Test Table
FIN_ID  GrossRev  Year  Month
 1             1000         2015   1
 2             500           2015   1
 1             1000         2015   2

The report should look like
1             1000         2015    1
2              500          2015    1
1              1000        2015    2
2                0             2015    2

Thank you for all of your help
thomasm1948Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
You need to use LEFT JOIN to get that data

http://www.w3schools.com/sql/sql_join_left.asp
0
thomasm1948Author Commented:
I would but how would I do not that on one table.
0
lcohanDatabase AnalystCommented:
should be something like
SELECT t1.FIN_ID,  t2.GrossRev,  t1.Year,  t1.Month,
 FROM Test_Table AS t1
 LEFT JOIN Test_Table AS t2
 ON t1.month=t2.month;
0
Ryan ChongCommented:
try:
Select a.FIN_ID, IFNULL(b.GrossRev,0) GrossRev, a.Year, a.Month
From
(
  SELECT b.FIN_ID, a.Year, a.Month
  FROM Test a, (select a.FIN_ID from Test a Group By a.FIN_ID) b
) a
LEFT JOIN Test b
on a.FIN_ID = b.FIN_ID and a.Year = b.Year and a.Month = b.Month
Group By a.FIN_ID, b.GrossRev, a.Year, a.Month
Order By a.Year, a.Month, a.FIN_ID

Open in new window

0
Ryan ChongCommented:
or similarly, customize this:
Select a.FIN_ID, IFNULL(b.GrossRev,0) GrossRev, a.Year, a.Month
From
(
  SELECT b.FIN_ID, a.Year, a.Month
  FROM Test a, (select a.FIN_ID from Test a Group By a.FIN_ID) b
  Group By b.FIN_ID, a.Year, a.Month  
) a
LEFT JOIN Test b
on a.FIN_ID = b.FIN_ID and a.Year = b.Year and a.Month = b.Month
Order By a.Year, a.Month, a.FIN_ID

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.