How to pair distinct values from same column on MySQL

I have a table, it has a code and a value, in one select statement (no cursors, temp tables) I need to select the list of pairings as (CODE1, CODE2) from the code column without repeating again with (CODE2, CODE1) Ex.

CODE1, CODE2
CODE1, CODE3
CODE1, CODE4
CODE2, CODE1 <---Value Repeated before as CODE1, CODE2
CODE2, CODE3
CODE2, CODE4
....

Help please.
aisinfoAsked:
Who is Participating?
 
Roger BaklundConnect With a Mentor Commented:
I understand that you need the code twice, but it is not clear what you are trying to achieve. What is the criteria in the WHERE clause? Are you using a self join? Are you trying to find duplicates?

I guess you are using a self join to find duplicates, and the where clause contains A.value=B.value and A.code!=B.code?

Try changing A.code!=B.code to A.code<B.code:
select A.code,B.code
from MyTable A, MyTable B
where 
  A.value = B.value and
  A.code < B.code

Open in new window

0
 
Roger BaklundCommented:
The question is confusing, you talk about a code and a value, but the result seems too contain the code twice? Can you show your current query?
0
 
SharathData EngineerCommented:
This query will give you all records except the below.
CODE1, CODE2
CODE2, CODE1

I am still looking to get one record from the above two records..

SELECT DISTINCT A.Col1,A.Col2
  FROM YourTable A
  JOIN YourTable B
    ON A.Col1 <> B.Col2
   AND A.Col2 <> B.Col1

Open in new window

0
 
aisinfoAuthor Commented:
Yes, it's supposed to be code twice, it's pairing the codes on that column.
0
 
aisinfoAuthor Commented:
So simple, All I was missing was the <. Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.