MySQL Left join question

hi All,

I have a situation where I have created a left join and everything is great if I run it for the same year and month (column names in a table) .  For Example, If a franchise has not submitted any information for a certain year and month then it shows zeros for the null values and then places 2015 in the year column and 9 for the month column

The question that I have is if I choose to run the report for the whole year how can I add the proper year and month for the franchises that did not post anything for that month and year
thomasm1948Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Jim HornMicrosoft SQL Server Data DudeCommented:
Kinda hard to visualize this question without some sample data.
Offhand, sounds like you need a 'lookup' table of all years and months.
thomasm1948Author Commented:
let's say if we have 2 tables:
table 1: franchise
fr_num
   1
   2
   3

Table2: royalties
fr_num  grossrev  year   month
    1            1000     2015       9
    2            2000     2015       9
    1            3000     2015      10
    3            4000     2015      10

Report should look like
fr_num  grossrev  year   month
    1            1000     2015       9
    2            2000     2015       9
    3             0.00     2015       9
    1            3000     2015      10
    2             0.00     2015      10
    3            4000     2015      10
thomasm1948Author Commented:
Ok this is what i came up with, but I am unsure if this is the best method

DELIMITER //
CREATE PROCEDURE Gross_Royalty_Report (
IN R_Month INT,
IN R_Year Int
)
BEGIN
  DECLARE i INT DEFAULT 1;
  CREATE TEMPORARY TABLE BOB1 (
     FIN_NUM INT,
     Business_Entity varchar (50)
    );
    CREATE TEMPORARY TABLE BOB2 (
     FIN_NUM INT,
     GRoyalty DECIMAL(30,2),    
     RMonth INT,
     RYear Int
    );  
    CREATE TEMPORARY TABLE BOB3 (
     FIN_NUM INT,
     Business_Entity varchar (50),
     GRoyalty DECIMAL(30,2),    
     RMonth INT,
     RYear Int
    );  
     -- insert data BOB1
      INSERT INTO BOB1 (FIN_NUM, Business_Entity) VALUES (0001, 'test1');
      INSERT INTO BOB1 (FIN_NUM, Business_Entity) VALUES (0002, 'test2');
      INSERT INTO BOB1 (FIN_NUM, Business_Entity) VALUES (0003, 'test3');
     -- insert data BOB2
    INSERT INTO BOB2 (FIN_NUM, GRoyalty, RMonth, RYear) VALUES (0001, 1000, 1, 2015);
    INSERT INTO BOB2 (FIN_NUM, GRoyalty, RMonth, RYear) VALUES (0001, 2000, 2, 2015);
    INSERT INTO BOB2 (FIN_NUM, GRoyalty, RMonth, RYear) VALUES (0002, 4000, 1, 2015);
    INSERT INTO BOB2 (FIN_NUM, GRoyalty, RMonth, RYear) VALUES (0003, 1000, 3, 2015);
    WHILE (i <= R_Month) DO
      INSERT INTO BOB3 (FIN_NUM, Business_Entity, GRoyalty, RMonth, RYear)
          SELECT B1.FIN_NUM, B1.business_Entity, IFNULL(B2.GRoyalty,0), IFNULL(B2.RMonth,i),
        IFNULL(B2.RYear,R_Year)
        FROM BOB1 B1
        LEFT JOIN (SELECT BB.FIN_NUM, BB.GRoyalty, BB.RMonth, BB.RYear
                           FROM BOB2 BB
                   WHERE BB.RMonth = i ) B2 ON B1.FIN_NUM = B2.FIN_NUM
        ORDER BY B1.FIN_NUM
        ;
     
      -- VALUES (i, s_year);
        SET i = i+1;
 

     END WHILE;
     SELECT * FROM BOB3;
     DROP TEMPORARY TABLE BOB1;
     DROP TEMPORARY TABLE BOB2;
     DROP TEMPORARY TABLE BOB3;
     
END //
DELIMITER ;

CALL `test`.`Gross_Royalty_Report`(3,2015);
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Julian HansenCommented:
I am probably oversimplifying this but why does this not work for you
Create a table of dates and months and left join to that.
Hamed NasrRetired IT ProfessionalCommented:
Try this:
SELECT c.fr_num, IFNULL(d.grossrev,0.00) As grossrev, c.year, c.month
 FROM (SELECT a.fr_num, b.year,b.month
FROM franchise as a, (Select distinct year, month from royalties) as b) as c left join royalties as d on c.fr_num=d.fr_num and c.year=d.year AND c.month=d.month
Order By c.month;

Open in new window

PortletPaulEE Topic AdvisorCommented:
This result:
| year | month | fr_num | grossrev |
|------|-------|--------|----------|
| 2015 |     9 |      1 |     1000 |
| 2015 |     9 |      2 |     2000 |
| 2015 |     9 | (null) |   (null) |
| 2015 |    10 |      1 |     3000 |
| 2015 |    10 |      3 |     4000 |
| 2015 |    10 | (null) |   (null) |

Open in new window

From the following query. You need a "cross join" (Cartesian product) of (distinct) periods and franchises, THEN left join your revenues to that result.
select
      p.`year`
    , p.`month`
    , r.`fr_num`
    , r.`grossrev`
from (select distinct `year`, `month` from royalties) as p
    cross join franchise as f
    left join royalties as r on p.`year` = r.`year`
                            and p.`month`= r.`month`
                            and f.`fr_num` = r.`fr_num`
order by
      p.`year`
    , p.`month`
;

Open in new window

data:
CREATE TABLE  franchise
    (`fr_num` int)
;
    
INSERT INTO  franchise
    (`fr_num`)
VALUES
    (1),
    (2),
    (3)
;

CREATE TABLE royalties
    (`fr_num` int, `grossrev` int, `year` int, `month` int)
;
    
INSERT INTO royalties
    (`fr_num`, `grossrev`, `year`, `month`)
VALUES
    (1, 1000, 2015, 9),
    (2, 2000, 2015, 9),
    (1, 3000, 2015, 10),
    (3, 4000, 2015, 10)
;

Open in new window

also see: http://sqlfiddle.com/#!9/afc565/1

Note this assumes your revenues table holds at least one reference to ever period.
Hamed NasrRetired IT ProfessionalCommented:
Same query as in my previous comment (http:#a41045208), showing your sample data and the query result. IFNULL() function is used to replace NULL, in result,  by 0.00

Table franchise:
+--------+
| fr_num |
+--------+
|      1 |
|      2 |
|      3 |
+--------+

Open in new window

Table royalties:
+--------+----------+------+-------+
| fr_num | grossrev | year | month |
+--------+----------+------+-------+
|      1 |     1000 | 2015 |     9 |
|      2 |     2000 | 2015 |     9 |
|      1 |     3000 | 2015 |    10 |
|      3 |     4000 | 2015 |    10 |
+--------+----------+------+-------+

Open in new window

Query:
Cross-join franchise table with a query including year and month from royalties; this cross-join is left joined with royalties, as shown bellow:
SELECT c.fr_num, IFNULL(d.grossrev,0.00) As grossrev, c.year, c.month
 FROM (SELECT a.fr_num, b.year,b.month
FROM franchise as a, (Select distinct year, month from royalties) as b) as c left join royalties as d on c.fr_num=d.fr_num and c.year=d.year AND c.month=d.month
Order By c.month;

Open in new window

Result:
+--------+----------+------+-------+
| fr_num | grossrev | year | month |
+--------+----------+------+-------+
|      1 |  1000.00 | 2015 |     9 |
|      2 |  2000.00 | 2015 |     9 |
|      3 |     0.00 | 2015 |     9 |
|      3 |  4000.00 | 2015 |    10 |
|      1 |  3000.00 | 2015 |    10 |
|      2 |     0.00 | 2015 |    10 |
+--------+----------+------+-------+

Open in new window

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
PortletPaulEE Topic AdvisorCommented:
Another approach is to use a "tally table" which is a utility that can be used in many situations
(it simply stored integers from 1 to (a number you specify))

Here the tally table is used to generate all months from 2015-01-01 for 12 months, and then the cross join and left join are used

[SQL Fiddle][1]

**MySQL 5.6 Schema Setup**:

    /* process code from http://www.solomonson.com/content/how-create-tally-table-mysql */
    /* Create the tally table.*/
    CREATE TABLE tally (
        id int unsigned NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (id)
    );
     
    /* Populate it */
    
    CREATE PROCEDURE create_tally()
    BEGIN
        SET @x = 0;
        WHILE @x<1000 DO
            /* Insert a single row into tally. */
            INSERT INTO tally (id) VALUES (NULL);
            SET @x = @x + 1;
        END WHILE;
    END//
    
    CALL create_tally();
    //
    
    CREATE TABLE  franchise
        (`fr_num` int)
    ;
        
    INSERT INTO  franchise
        (`fr_num`)
    VALUES
        (1),
        (2),
        (3)
    ;
    
    CREATE TABLE royalties
        (`fr_num` int, `grossrev` int, `year` int, `month` int)
    ;
        
    INSERT INTO royalties
        (`fr_num`, `grossrev`, `year`, `month`)
    VALUES
        (1, 1000, 2015, 9),
        (2, 2000, 2015, 9),
        (1, 3000, 2015, 10),
        (3, 4000, 2015, 10)
    ;
    
**Query 1**:

    select
          p.`year`
        , p.`month`
        , f.`fr_num`
        , r.`grossrev`
    from ( select
                  year(date_add('2015-01-01', INTERVAL `id`-1 MONTH)) as `year`
                , month(date_add('2015-01-01', INTERVAL `id`-1 MONTH)) as `month`
            from tally
            where id < 12 
          ) as p
        cross join franchise as f
        left join royalties as r on p.`year` = r.`year` and p.`month` = r.`month`
    order by 
          p.`year`
        , p.`month`
        , f.`fr_num`
    

**[Results][2]**:
    | year | month | fr_num | grossrev |
    |------|-------|--------|----------|
    | 2015 |     1 |      1 |   (null) |
    | 2015 |     1 |      2 |   (null) |
    | 2015 |     1 |      3 |   (null) |
    | 2015 |     2 |      1 |   (null) |
    | 2015 |     2 |      2 |   (null) |
    | 2015 |     2 |      3 |   (null) |
    | 2015 |     3 |      1 |   (null) |
    | 2015 |     3 |      2 |   (null) |
    | 2015 |     3 |      3 |   (null) |
    | 2015 |     4 |      1 |   (null) |
    | 2015 |     4 |      2 |   (null) |
    | 2015 |     4 |      3 |   (null) |
    | 2015 |     5 |      1 |   (null) |
    | 2015 |     5 |      2 |   (null) |
    | 2015 |     5 |      3 |   (null) |
    | 2015 |     6 |      1 |   (null) |
    | 2015 |     6 |      2 |   (null) |
    | 2015 |     6 |      3 |   (null) |
    | 2015 |     7 |      1 |   (null) |
    | 2015 |     7 |      2 |   (null) |
    | 2015 |     7 |      3 |   (null) |
    | 2015 |     8 |      1 |   (null) |
    | 2015 |     8 |      2 |   (null) |
    | 2015 |     8 |      3 |   (null) |
    | 2015 |     9 |      1 |     2000 |
    | 2015 |     9 |      1 |     1000 |
    | 2015 |     9 |      2 |     2000 |
    | 2015 |     9 |      2 |     1000 |
    | 2015 |     9 |      3 |     1000 |
    | 2015 |     9 |      3 |     2000 |
    | 2015 |    10 |      1 |     3000 |
    | 2015 |    10 |      1 |     4000 |
    | 2015 |    10 |      2 |     3000 |
    | 2015 |    10 |      2 |     4000 |
    | 2015 |    10 |      3 |     3000 |
    | 2015 |    10 |      3 |     4000 |
    | 2015 |    11 |      1 |   (null) |
    | 2015 |    11 |      2 |   (null) |
    | 2015 |    11 |      3 |   (null) |

  [1]: http://sqlfiddle.com/#!9/8f5c4/1
  [2]: http://sqlfiddle.com/#!9/8f5c4/1/0

Open in new window

Note the process code to generate the tally was borrowed and I reduced the size as I didn't need many records for this demonstration.
PortletPaulEE Topic AdvisorCommented:
I should point out that hnsar proposed use of a Cartesian product before I did.

It's my strong preference to always use an explicit CROSS JOIN when you are employing that technique.
thomasm1948Author Commented:
Thank you all for your help
Hamed NasrRetired IT ProfessionalCommented:
Welcome!
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.