How to join two unrelated tables in sql

Question

I have two tables:

Table 1: Formulas

FormulaId    Formula Text
1            [Qty] * [Rect]
2            [Qty] * [Al]
3            [Mt] * [Cat]  

Table 2: Context

ContextId    Name
1            Test 1
2            Test 2
3            Test 3
4            Test 4    

I need to join those somehow in sql server 2008 R2 to get a table where for each context id I will have a full list of formulas, i.e.

Result

ContextId    Name     FormulaId    Formula Text    
1            Test 1   1            [Qty] * [Rect]
1            Test 1   2            [Qty] * [Al]
1            Test 1   3            [Mt] * [Cat]
2            Test 2   1            [Qty] * [Rect]
2            Test 2   2            [Qty] * [Al]
2            Test 2   3            [Mt] * [Cat]
3            Test 3   1            [Qty] * [Rect]
3            Test 3   2            [Qty] * [Al]
3            Test 3   3            [Mt] * [Cat]
4            Test 4   1            [Qty] * [Rect]
4            Test 4   2            [Qty] * [Al]
4            Test 4   3            [Mt] * [Cat]

Solution 1

You want to use a CROSS JOIN:

SELECT FormulaId, Formula, ContextId, [Name]
FROM Formula
CROSS JOIN Context

Solution 2

You can use the Cartesian Product of the two tables as follows:

SELECT * FROM Formulas, Context

This would result in M * N rows.

Solution 3

Did you try CROSS APPLY:

select *
from context
cross apply formulas
order by contextid

See SQL Fiddle With Demo

Comments

[LittleBobbyTables - Au Revoir]

I've got to get better at typing on the phone. Autocorrect doesn't seem to understand SQL.

[Vasanth]

How can I pull specific columns from two completely unrelated tables?