how do I merge two columns and auto increment primary key after using JOIN in my table?
Posted by:
Gopi Shukla
Date: September 27, 2020 03:15PM
Table A:
ACCOUNT_
NUMBER (PK) LOAN_TYPE info_1
50 Housing Random1
51 Car Random4
52 Small business Random7
53 Small business Random10
54 Small business Random13
Table B:
ACCOUNT_
NUM (PK) PREV_ACCT_NUM (FK) ACCOUNT_TYPE Var_1
100 50 Housing R1
101 51 Car R4
102 52 Small business R7
103 7 Housing R10
104 61 car R13
OUTPUT TABLE_
ACCOUNT_. ** New merged Prev ** New Merged
NUM (PK) Account Number ACCOUNT_TYPE Var_1 Info_1
100 50 Housing R1 Random1
101 51 Car R4 Random4
102 52 Small business R7 Random7
103 7 Housing R10
104 61 Car R13
105## 53 Small business Random10
106## 54 Small business Random13
I want to do full outer join to merge two tables on condition matching (PREV_ACCT_NUM & ACCOUNT_TYPE)From Table B with (ACCOUNT_NUMBER and LOAN_TYPE) from Table_A.
**On matching I want to merge these two matched columen shown in **
##Also for non-matched items in Table B, I want to autoincrement Account_NUM
I used below script and I need help with tuning to include autoincrement and column merge functionality after join.
CREATE TABLE `table_A` (
`ACCOUNT_NUMBER` int NOT NULL,
`LOAN_TYPE` varchar(45) DEFAULT NULL,
`Var1` varchar(45) DEFAULT NULL,
PRIMARY KEY (`ACCOUNT_NUMBER`),
UNIQUE KEY `ACCOUNT_NUMBER_UNIQUE` (`ACCOUNT_NUMBER`)
)
INSERT INTO `database1`.`table_A` (`ACCOUNT_NUMBER`, `LOAN_TYPE`, `Var1`) VALUES ( '50', 'Housing', 'a');
INSERT INTO `database1`.`table_A` (`ACCOUNT_NUMBER`, `LOAN_TYPE`, `Var1`) VALUES ( '51’, ‘Car’, ‘b’);
INSERT INTO `database1`.`table_A` (`ACCOUNT_NUMBER`, `LOAN_TYPE`, `Var1`) VALUES ( '52’, ‘Corporate’, ‘c’);
INSERT INTO `database1`.`table_A` (`ACCOUNT_NUMBER`, `LOAN_TYPE`, `Var1`) VALUES ( '53’, ‘Small business’, ‘d’);
INSERT INTO `database1`.`table_A` (`ACCOUNT_NUMBER`, `LOAN_TYPE`, `Var1`) VALUES ( '54’, ‘Small business’, ‘e’);
CREATE TABLE `table_B` (
`ACCOUNT_NUM` int NOT NULL AUTO_INCREMENT,
`PREV_ACCT_NUM` int DEFAULT NULL,
`ACCOUNT_TYPE` varchar(45) DEFAULT NULL,
`info1` varchar(45) DEFAULT NULL,
PRIMARY KEY (`ACCOUNT_NUM`),
UNIQUE KEY `ACCOUNT_NUM_UNIQUE` (`ACCOUNT_NUM`)
)
INSERT INTO `database1`.`table_B` (`ACCOUNT_NUM`, `PREV_ACCT_NUM`, `ACCOUNT_TYPE`, `info1`) VALUES ('100', '50', 'Housing', 'aa');
INSERT INTO `database1`.`table_B` (`ACCOUNT_NUM`, `PREV_ACCT_NUM`, `ACCOUNT_TYPE`, `info1`) VALUES ('101', '51', 'Car', 'bb');
INSERT INTO `database1`.`table_B` (`ACCOUNT_NUM`, `PREV_ACCT_NUM`, `ACCOUNT_TYPE`, `info1`) VALUES ('102', '52', 'Corporate', 'cc');
INSERT INTO `database1`.`table_B` (`ACCOUNT_NUM`, `PREV_ACCT_NUM`, `ACCOUNT_TYPE`, `info1`) VALUES ('103', '7', 'Small business', 'dd');
INSERT INTO `database1`.`table_B` (`ACCOUNT_NUM`, `PREV_ACCT_NUM`, `ACCOUNT_TYPE`, `info1`) VALUES ('104', '61', 'Small business', 'ee');
SELECT * FROM database1.table_A;
SELECT * FROM database1.table_B;
SELECT DISTINCT * FROM database1.table_B AS b
LEFT JOIN database1.table_A AS a
ON a.ACCOUNT_NUMBER = b.PREV_ACCT_NUM AND a.LOAN_TYPE = b.ACCOUNT_TYPE
UNION
SELECT DISTINCT * FROM database1.table_B AS b
RIGHT JOIN database1.table_A AS a
ON a.ACCOUNT_NUMBER = b.PREV_ACCT_NUM AND a.LOAN_TYPE = b.ACCOUNT_TYPE
WHERE a.ACCOUNT_NUMBER IS NULL OR b.PREV_ACCT_NUM IS NULL;