MySQL Forums
Forum List  »  Newbie

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;

Options: ReplyQuote


Subject
Written By
Posted
how do I merge two columns and auto increment primary key after using JOIN in my table?
September 27, 2020 03:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.