CODEWITHSUNDEEP
t-sqlpermutation
Niels Bosma
Niels Bosma

Reputation: 11528

How can I get permutations of items from two subqueries in T-SQL?

Lets say I have two subqueries:

SELECT Id AS Id0 FROM Table0

=>

Id0
---
1
2
3

and

SELECT Id AS Id1 FROM Table1

=>


Id1
---
4
5
6

How do I combine these to get the query result:

Id0 Id1
-------
1   4
1   5
1   6
2   4
2   5
2   6
3   4
3   5
3   6

Upvotes: 1

Views: 476

Answers (3)

TheKido
TheKido

Reputation: 109

SELECT Table0.Id0, Table1.Id1 FROM Table0 Full Join Table1 on 1=1

Upvotes: 1

codeulike
codeulike

Reputation: 23074

Cartesian join, a join with no join condition

select id0.id as id0, id1.id as id1 
from id0, id1

alternatively you can use the CROSS JOIN syntax if you prefer

select id0.id as id0, id1.id as id1 
from id0 cross join id1

you can order your query if you want a specific order, from your example it looks like you want

select id0.id as id0, id1.id as id1
from id0 cross join id1 order by id0.id, id1.id

Upvotes: 1

podosta
podosta

Reputation: 1962

Try this :

SELECT A.Id0, B.Id1
FROM (SELECT Id AS Id0 FROM Table0) A, 
     (SELECT Id AS Id1 FROM Table1) B

Grégoire

Upvotes: 1

Related Questions