Link to home
Start Free TrialLog in
Avatar of thomasm1948
thomasm1948Flag for United States of America

asked on

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
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Kinda hard to visualize this question without some sample data.
Offhand, sounds like you need a 'lookup' table of all years and months.
Avatar of thomasm1948

ASKER

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
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);
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.
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

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.
ASKER CERTIFIED SOLUTION
Avatar of Hamed Nasr
Hamed Nasr
Flag of Oman image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Thank you all for your help
Welcome!