CODEWITHSUNDEEP
mysql
Felipe
Felipe

Reputation: 9431

mysql find all the possible combinations of one column

I have the table UserContacts with a column like

contact_id

and I want to get the following result (which is all the possible pair combinations of the values of that column)... (1,5),(1,6),(5,6)

is it possible?

Upvotes: 0

Views: 3277

Answers (3)

Rafael Felipe Knevels
Rafael Felipe Knevels

Reputation: 1

USE CROSS JOIN

SELECT a.contact_id FROM UserContacts a CROSS JOIN UserContacts b

Upvotes: -2

cardeol
cardeol

Reputation: 2238

This should work

SELECT t2.contact_id,t1.contact_id FROM UserContacts t1 JOIN UserContacts t2 
WHERE t1.contact_id <> t2.contact_id

Upvotes: 1

eggyal
eggyal

Reputation: 126005

Sure, make a self-join:

SELECT a.contact_id a, b.contact_id b
FROM   UserContacts a
  JOIN UserContacts b ON b.contact_id > a.contact_id

See it on sqlfiddle.

Upvotes: 4

Related Questions