# Thread: Finding all possible unique combinations of values

1. Registered User
Join Date
Mar 2013
Posts
3

## Unanswered: Finding all possible unique combinations of values

Hello,
I am looking for thoughts on how to find all possible combinations of values, for example:

My table includes:
Code:
CREATE TABLE temp1 (item varchar(50), ORDER int);

INSERT INTO temp1 (item, order) VALUES ('apple',1);
INSERT INTO temp1 (item, order) VALUES ('pear',2);
INSERT INTO temp1 (item, order) VALUES ('blueberry',3);

I need the output to be like this:

apple
pear
blueberry
apple, pear
apple, blueberry
pear, blueberry
apple, pear, blueberry

I don't need the reverse of each. For example, I need only 'apple, pear'... I don't need 'pear, apple'.

Would love some guidance!

2. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Homework assignment?
You need the output to be a comma-separated list?
One way to do this would be to add a fourth item with null as item. A cross-join would then give all the permutations.

3. Registered User
Join Date
Mar 2013
Posts
3
Not a homework assignment I only wish I was still in school!

In the real data, I have 10 variables that I need to find all possible combinations.

The end result is I will be creating segments of people that fit the criteria of the segment... but first step is to know how many segments I will end up with and how to actually create them.

I've not ever worked with cross join before. I will research that a bit, but any add'l tips you could give would be great.

4. Registered User
Join Date
Mar 2013
Posts
3
I think I've got it, but not using cross join... however it may not be the most efficient.

Code:
select al.* from
(select distinct a.item as item1, b.item as item2, c.item as item3
from temp1 a
left join
(select distinct b.item
from temp1 b) b
on a.item <> b.item
left join
select disinct c.item
from temp1 c) c
on a.item <> c.item and
b.item <> c.item
where b.item <> '' -- blank row added
) al
where item1 <>''
order by item1, item2, item3

5. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
A cross join is just a join "without joins", essentially matching each record in one data set with every record in the other dataset.
For three variables, you'll need a three-way cross-join:
Code:
select	a.item as itema,
b.item as itemb,
c.item as itemc
from	temp1 as a,
temp1 as b,
temp1 as c
where	a.item < b.item
and b.item < c.item --Eliminates duplicated items
... but this will not give you combinations of just one or two items. To get that, at a fourth "phantom" item to your records.

6. Registered User
Join Date
Jan 2013
Posts
355

## I did this one earlier this year ..

DROP TABLE Elements;

CREATE TABLE Elements
(e CHAR(1) NOT NULL PRIMARY KEY);

INSERT INTO Elements
VALUES ('a'),('b'),('c'),('d');

Permutations:
SELECT E1.e, E2.e, E3.e, E4.e
FROM Elements AS E1, Elements AS E2, Elements AS E3
WHERE E1.e < E2.e
AND E2.e < E3.e
AND E3.e < E4.e
GROUP BY CUBE (E1.e, E2.e, E3.e, E4.e);

--combinations;
SELECT E1.e, E2.e, E3.e, E4.e
FROM Elements AS E1, Elements AS E2,
Elements AS E3, Elements AS E4
WHERE E1.e NOT IN (E2.e, E3.e, E4.e)
AND E2.e NOT IN (E3.e, E4.e)
AND E3.e NOT IN (E4.e)
GROUP BY CUBE (E1.e, E2.e, E3.e, E4.e)
ORDER BY E1.e, E2.e, E3.e, E4.e;

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•