How to remove duplicate rows from a join query in mysql

Question

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

John Woo · Accepted Answer · 2013-03-25 13:58:03Z

4

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

SQLFiddle Demo

OUTPUT

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

answered Mar 25, 2013 at 13:58

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1926138 Over a year ago

yes its working fine. can you please explain the query to me how a.Name < b.Name is working??

jurgenreza · Accepted Answer · 2013-03-25 14:15:56Z

2

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.

edited Mar 25, 2013 at 14:15

answered Mar 25, 2013 at 13:54

jurgenreza

6,1042 gold badges30 silver badges37 bronze badges

5 Comments

fancyPants Over a year ago

@user1926138 With your example given this answer fullfils the requirements. +1

jurgenreza Over a year ago

@user1926138 it will give you exact same result. try it.

jurgenreza Over a year ago

@tombom thanks for the edit, both where clause and on clause give the same result I'm not sure which one is the better practice.

fancyPants Over a year ago

Your join table where... is a raped ANSI-SQL standard. Either you have a ON clause when using JOIN or you build a cartesian product like FROM table1 t1, table2 t2 WHERE t1.id = t2.id. Do not mix both ways. But the former one, using explicit join syntax is the preferred one. It was introduced because the latter one was too error prone and is worse to read.

jurgenreza Over a year ago

@user1926138 I added an explanation. Hope it helps.

Collectives™ on Stack Overflow

How to remove duplicate rows from a join query in mysql

2 Answers 2

1 Comment

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related