CODEWITHSUNDEEP
mysqlsql
Deep Shah
Deep Shah

Reputation: 1364

How to get all possible combinations among rows of the same table

I need combination of the rows from the same table . Is it possible ? Here is my table :

CREATE TABLE `ta` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)


INSERT INTO `ta` (`id`, `name`) VALUES 
(1, 'ta1'),
(2, 'ta2'),
(3, 'ta3'),
(4, 'ta4');

Result should be like below :

|    ta1 |    ta2 |
|    ta1 |    ta3 |
|    ta1 |    ta4 |
|    ta2 |    ta3 |
|    ta2 |    ta4 |
|    ta3 |    ta4 |

OR 

|    ta1 |    ta2 |
|    ta1 |    ta3 |
|    ta1 |    ta4 |
|    ta2 |    ta1 |
|    ta2 |    ta3 |
|    ta2 |    ta4 |
|    ta3 |    ta1 |
|    ta3 |    ta2 |
|    ta3 |    ta4 |
|    ta4 |    ta1 |
|    ta4 |    ta2 |
|    ta4 |    ta3 |

Upvotes: 2

Views: 852

Answers (2)

1000111
1000111

Reputation: 13519

You need to make CROSS JOIN between the two instances of your table ta say A & B

SELECT 
 A.name,
 B.name FROM ta A CROSS JOIN ta B 
WHERE A.id <> B.id

See Demo

Note:

If you consider <a,b> and <b,a> entries differently then the above query would suffice.

And if you want only one record for each pair where order doesn't matter (i.e. <a,b> is equal to <b,a>) then adopt the following instead:

SELECT 
 A.name,
 B.name FROM ta A CROSS JOIN ta B 
WHERE A.id < B.id

Upvotes: 3

jarlh
jarlh

Reputation: 44696

Do a self join:

SELECT t1.name, t2.name
FROM ta t1
    JOIN ta t2 ON t1.id < t2.id

Upvotes: 1

Related Questions