SO46306405, MySQL - rextester

Language: Layout:

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

drop table if exists SO46306405_tbl_A;
drop table if exists SO46306405_tbl_B;
drop table if exists SO46306405_tbl_C;

Create table SO46306405_tbl_A (
    cap_Id int,
    yr_A int,
    sp_a varchar(3),
    iso_a int,
    area_a int,
    qty_a numeric(10,2));
 
 Create table SO46306405_tbl_B (
    aqua_Id int,
    yr_b int,
    sp_b varchar(3),
    iso_b int,
    area_b int,
    qty_b numeric(10,2));

Create table SO46306405_tbl_C (
    cap_id int,
    aqua_id int,
    yr_a int,
    yr_b int,
    qty_a numeric(10,2),
    qty_b numeric(10,2));
      
INSERT INTO SO46306405_tbl_A values(3, 2015,  'TRR',    54,      8,   120);
INSERT INTO SO46306405_tbl_A values(678, 2015,  'BOM',    62,     27,   0.0);
INSERT INTO SO46306405_tbl_A values(20, 2015,  'TRR',    54,     27,   0.0);
INSERT INTO SO46306405_tbl_A values(45, 2015,  'FRC',     7,     15, 86800);
#INSERT INTO SO46306405_tbl_A values(45, 2015,  'FRC',     7,     15, 86800);

INSERT INTO SO46306405_tbl_B values(78, 2015,  'OTS',    32,     27,  6868);
INSERT INTO SO46306405_tbl_B values(333, 2015,  'FRC',     7,     15,   550);
INSERT INTO SO46306405_tbl_B values(789, 2015,  'TRR',    54,     27, 45000);
INSERT INTO SO46306405_tbl_B values(987, 2015,  'TRR',    32,     27,    40);
#INSERT INTO SO46306405_tbl_B values(987, 2015,  'TRR',    32,     27,    40);

(SELECT a.cap_id, b.aqua_id, a.yr_a, b.yr_b, a.qty_a, b.qty_b
FROM SO46306405_tbl_A AS a LEFT JOIN SO46306405_tbl_B AS b
ON a.yr_a = b.yr_b 
AND a.iso_a = b.iso_b
AND a.area_a = b.area_b
AND a.sp_a = b.sp_b
WHERE a.yr_a = 2015)
UNION
(SELECT a.cap_id, b.aqua_id, a.yr_a, b.yr_b, a.qty_a, b.qty_b
FROM  SO46306405_tbl_B AS b LEFT JOIN SO46306405_tbl_A AS a
ON a.yr_a = b.yr_b
AND a.iso_a = b.iso_b
AND a.area_a = b.area_b
AND a.sp_a = b.sp_b
WHERE b.yr_b = 2015);

Insert into SO46306405_tbl_C SELECT * FROM (
#and here's my example left/right w/ exclusions to eliminate extra duplicates when using  union all. 
(SELECT a.cap_id, b.aqua_id, a.yr_a, b.yr_b, a.qty_a, b.qty_b
FROM SO46306405_tbl_A AS a 
LEFT JOIN SO46306405_tbl_B AS b
  ON a.yr_a = b.yr_b 
 AND a.iso_a = b.iso_b
 AND a.area_a = b.area_b
 AND a.sp_a = b.sp_b
WHERE a.yr_a = 2015
  and (a.yr_a is not null 
   or a.iso_a is not null 
   or a.area_a is not null 
   or a.sp_a is not null))
UNION ALL
(SELECT a.cap_id, b.aqua_id, a.yr_a, b.yr_b, a.qty_a, b.qty_b
FROM SO46306405_tbl_A AS a 
RIGHT JOIN SO46306405_tbl_B AS b
   ON a.yr_a = b.yr_b 
  AND a.iso_a = b.iso_b
  AND a.area_a = b.area_b
  AND a.sp_a = b.sp_b
WHERE b.yr_b = 2015
  and (b.yr_b is not null 
   or b.iso_b is not null 
   or b.area_b is not null 
   or b.sp_b is not null)
 and a.yr_a is null  #note what happens if you don't have this line you end up with 8 records.
 )
 order by cap_Id, aqua_ID) Sub;
 
 SELECT * FROM SO46306405_tbl_C;

drop table if exists SO46306405_tbl_A;
drop table if exists SO46306405_tbl_B;
drop table if exists SO46306405_tbl_C;


absolute service time: 0,38 sec