asked on

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

lcohan

You need to use LEFT JOIN to get that data

http://www.w3schools.com/sql/sql_join_left.asp

thomasm1948

ASKER

I would but how would I do not that on one table.

SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ryan Chong

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

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial