CODEWITHSUNDEEP
mysqlsql
John Smith
John Smith

Reputation: 6207

How to make combination matrix in mysql?

I have some names:

ID NAME
1   A
2   B
3   C
4   D

I want to insert them as a matrix:

ID NAME1 NAME2
     A     B
     A     C
     A     D
     B     C
     B     D
     C     D

so NAME1 <> NAME2 and a combination may occur only once.

Upvotes: 1

Views: 106

Answers (2)

Yasen Zhelev
Yasen Zhelev

Reputation: 4045

Or you can try that too:

SELECT t1.name, t2.name FROM table_name t1
LEFT JOIN table_name t2 ON t1.name != t2.name AND t1.ID < t2.ID
WHERE t2.name IS NOT NULL
ORDER BY t1.name ASC, t2.name ASC;

SQLFiddle demo here

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

Something like this:

SELECT t1.Name, t2.Name
FROM mytable AS t1
INNER JOIN mytable AS t2 ON t1.Name < t2.Name
ORDER BY t1.Name, t2.Name

The idea is to perform a self-join with all subsequent rows.

Demo here

Upvotes: 3

Related Questions