CODEWITHSUNDEEP
sql-server-2008t-sql
Sven Grosen
Sven Grosen

Reputation: 5636

Combine Two Disparate Result Sets Into One

I'm sure this has been asked before, but I can't think of how to phrase it in google, so here I am.

I have a table of users like so:

create table MyUser
(
    Id int
)

insert into MyUser(Id)
select 1 union
select 2 union
select 3 union
select 4 union
select 5

And a table of objects like so:

create table Object
(
    Id int
)

insert into Object(Id)
select 1 union
select 2 union
select 3

What I want as a result set is this (first column is MyUser.Id, second is Object.Id):

1, 1
1, 2
1, 3
2, 1
...

Basically, I want a row for every combination of these two tables. This question is similar, but I can't rely on values in one table being less than or greater than the values. So, basically, I believe what I want can be described as the cartesian product between two tables with no relationship.

How would I do that? I've been playing around with CTE's/subqueries and UNIONs, but I can't figure out how to filter out the dummy values for the UNION statements that represent the column not being selected in that part of the UNION and the CTE/subqueries may not be able to do what I'm wanting.

Upvotes: 0

Views: 292

Answers (2)

Hogan
Hogan

Reputation: 70513

Use cross join

SELECT * 
FROM MyUser 
CROSS JOIN Object

or using old style joins (which I don't recommend)

SELECT *
FROM MyUser, Object

Upvotes: 3

Menno
Menno

Reputation: 12621

Try something like:

SELECT * FROM MyUser, Object

Upvotes: 1

Related Questions