Create a table with all permutations of two source tables 1

[BobRodes]

I'm kind of at a loss as to how to formulate my question. Suppose I have two tables, temp0 and temp1. Temp0 contains a set of keys, and temp1 another, so:

Temp0
1
2
3

Temp1
a
b
c

I'm trying to work out a select that would result in this in a new table:

Temp2
key0 key1
1 a
1 b
1 c
2 a
2 b
2 c
3 a
3 b
3 c

I can't seem to work out how. I've tried using MERGE but can't quite set it up, as well as various other experiments. I could use a cursor but I just have the feeing there's a purely set-based solution that's eluding me. Is there?

TIA

An unforeseen consequence of the information revolution has been the exponential propagation of human error.

 

[imex]

Try using Cross Join:

select t0.Key0, t1.Key1
from Temp0 as t0
cross join Temp1 as t1
order by t0.Key0, t1.Key1

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[BobRodes]

That was precisely the solution. Thanks imex! I knew there was something simple that would solve this, never used cross joins before.

An unforeseen consequence of the information revolution has been the exponential propagation of human error.

 

[Motlatjo]

Try this


Select two.t0key, one.t1key
From(
Select t1key
From(
Select 'a' t1key
UNION
Select 'b' t1key
UNION
Select 'c' t1key) Temp0) one CROSS JOIN(



Select t0key
From(
Select 1 t0key
UNION
Select 2 t0key
UNION
Select 3 t0key) Temp1) TWO