CODEWITHSUNDEEP
sqlmysqldatabasehanadbeaver
Jaic E V
Jaic E V

Reputation: 1

SQL Query to query 2 tables which has duplicate values

Example Tables:

Table: TAB1

Col1 Col2 Col3 Col4
A1 A2 AA1 G1
A1 A2 AA1 G2

Table: TAB2

Col1 Col2 Col3 Col4
A1 A2 AA1 H1
A1 A2 AB1 H2

How to write a query to get Expected Result as:

Col1 Col2 Col3 Col4 Col5
A1 A2 AA1 G1 H1
A1 A2 AB1 G2 H2

I tried join but i was getting duplicate values

How to write a query to get Expected Result as:

Col1 Col2 Col3 Col4 Col5
A1 A2 AA1 G1 H1
A1 A2 AB1 G2 H2

Upvotes: 0

Views: 83

Answers (3)

Mahendra Varma
Mahendra Varma

Reputation: 101

The concept you are looking for is called "equi-join".

The following code should work:

SELECT TAB1.Col1,TAB1.Col2,TAB1.Col3,TAB1.Col4,TAB2.Col4 FROM TAB1,TAB2 WHERE TAB1.Col3=TAB2.Col3;

Upvotes: 0

Blue Water
Blue Water

Reputation: 190

SELECT Col1, Col2, Col3, Col4 FROM TAB1 UNION ALL SELECT Col1, Col2, Col3, Col4 as Col5 FROM TAB2;

Please use this query.

Upvotes: 1

P.Salmon
P.Salmon

Reputation: 17640

consider generating and joining on row number

DROP TABLE if exists t,t1;
create table t
(Col1 varchar(2), Col2 varchar(2),Col3 varchar(3), Col4  varchar(2));
insert into t values
('A1', 'A2', 'AA1', 'G1'),
('A1', 'A2', 'AA1', 'G2');

create table t1
(Col1 varchar(2), Col2 varchar(2),Col3 varchar(3), Col4  varchar(2));
insert into t1 values
('A1', 'A2', 'AA1', 'H1'),
('A1', 'A2', 'AB1', 'H2');

select a.col1,a.col2,b.col3,a.col4,b.col4 from
(
SELECT *,row_number() over (partition by col1,col2 order by col3,col4) rn FROM t
) a
join
(SELECT *,row_number() over (partition by col1,col2 order by col3,col4) rn FROM t1
)b on b.rn = a.rn;

https://dbfiddle.uk/vaLEwV7A

Upvotes: 0

Related Questions