CODEWITHSUNDEEP
mysqlsqljoin
user1926138
user1926138

Reputation: 1514

How to remove duplicate rows from a join query in mysql

I have a table having columns id and name

in my table 3 records present

id       name 
1        Chinmoy
2        Amit
3        Bhagi

I want result

name1     name2 
Amit      Bhagi
Amit      Chinmoy
Bhagi     chinmoy

I tried and succeeded up to this

name1    name2
Amit     Bhagi
Amit     Chinmoy
Bhagi    Amit
Bhagi    Chinmoy
Chinmoy  Amit
Chinmoy  Bhagi

by using this query

select tbl1.name,tbl2.name from test tbl1 
join test tbl1 on tbl1.name != tbl2.name
order by tbl1.name,tbl2.name;

Now i didn't get how to do.

I have to eliminate the record which are already present in opposite order.

Please Help

Thanks in advance

Upvotes: 1

Views: 9250

Answers (2)

jurgenreza
jurgenreza

Reputation: 6086

Try this:

select tbl1.name as n1, tbl2.name as n2 from test tbl1 
join test tbl2
on tbl1.name < tbl2.name
order by tbl1.name, tbl2.name;

Explanation:

You could add a condition tbl1.name < tbl2.name to eliminate duplicate values. This way you won't need the join condition you already have (tbl1.name != tbl2.name). Because when a < b, a is definitely not equal to b and also it sorts your names so that if Amit < Bhagi is true the opposite is not true and you won't also get Bhagi - Amit.

Upvotes: 2

John Woo
John Woo

Reputation: 263733

Basically, you can filter the result from the product of the two tables via a.Name < b.Name

SELECT  a.Name Name1, b.Name Name2
FROM    TableName a, TableName b
WHERE   a.Name < b.Name
ORDER   BY Name1, Name2

OUTPUT

╔═══════╦═════════╗
║ NAME1 ║  NAME2  ║
╠═══════╬═════════╣
║ Amit  ║ Bhagi   ║
║ Amit  ║ Chinmoy ║
║ Bhagi ║ Chinmoy ║
╚═══════╩═════════╝

Upvotes: 4

Related Questions