mysql 连接查询去除重复,如何从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

解决方案

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 ║

╚═══════╩═════════╝