Question 295549 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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
Tags:

3

Answers


  1. 0

    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;

    Login or Signup to reply.
  2. 0 
    SELECT Col1, Col2, Col3, Col4 FROM TAB1 UNION ALL SELECT Col1, Col2, Col3, Col4 as Col5 FROM TAB2;

    Please use this query.

    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search