thomasm1948
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
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
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
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
ASKER
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_Repo rt`(3,2015 );
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_Repo
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.
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;
This result:
Note this assumes your revenues table holds at least one reference to ever period.
| 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) |
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`
;
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)
;
also see: http://sqlfiddle.com/#!9/afc565/1Note this assumes your revenues table holds at least one reference to ever period.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
It's my strong preference to always use an explicit CROSS JOIN when you are employing that technique.
ASKER
Thank you all for your help
Welcome!
Offhand, sounds like you need a 'lookup' table of all years and months.