?
Solved

How to pair distinct values from same column on MySQL

Posted on 2008-11-18
5
Medium Priority
?
362 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:aisinfo
  • 2
  • 2
5 Comments
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22990451
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
 
LVL 41

Expert Comment

by:Sharath
ID: 22990485
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
 

Author Comment

by:aisinfo
ID: 22990511
Yes, it's supposed to be code twice, it's pairing the codes on that column.
0
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 2000 total points
ID: 22990668
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
 

Author Closing Comment

by:aisinfo
ID: 31518058
So simple, All I was missing was the <. Thanks
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question