i have a table with only one column consisiting of 5 colours-
colour
-------
red
black
white
green
orange
I want to get all the combinations like
(red,orange) (black,white) . . . etc except the identical ones.i have tried to cross join the table with itself .
select *
from table1 cross join table1
but i did not get the desired answer.it returned all the combinations.also identical ones.how can i get it???is there any other way to do this without creating another table???
If by identical you mean pairs like (white, white) perhaps this is what you want:
SELECT a.color, b.color
FROM colors a
CROSS JOIN colors b
WHERE a.color != b.color
If by identical you additionally mean preserve only one of (white, black) or (black, white) perhaps this is what you want:
SELECT a.color, b.color
FROM colors a
CROSS JOIN colors b
WHERE a.color > b.color
The important part is to reject the elements you don't want after you perform the cross-join.
Note that this won't create any new tables or modify existing ones. a and b are merely two different aliases for the same table colors. The table has only one column color, but since the table is present twice in the SELECT, you need to distinguish both (conceptual, not factual!) instances of the colors table.
You can't do without a join (then you'd have too few rows), nor you can easily do without aliases (you have to refer to both columns to reject some rows) nor there is a reason to assign aliases.
Combinations:
SELECT a.color,b.color
FROM colors a
JOIN colors b
ON a.color < b.color
Permutations:
SELECT a.color,b.color
FROM colors a
JOIN colors b
ON a.color != b.color
