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]
Using the “FROM Table1, Table2” Syntax One way to join two tables without a common column is to use an obsolete syntax for joining tables. With this syntax, we simply list the tables that we want to join in the FROM clause then use a WHERE clause to add joining conditions if necessary.
The join is done by the JOIN operator. In the FROM clause, the name of the first table ( product ) is followed by a JOIN keyword then by the name of the second table ( category ). This is then followed by the keyword ON and by the condition for joining the rows from the different tables.
SQL JOIN. A JOIN clause is used to combine rows from two or more tables, based on a related column between them. Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column.
You want to use a CROSS JOIN
:
SELECT FormulaId, Formula, ContextId, [Name] FROM Formula CROSS JOIN Context
You can use the Cartesian Product
of the two tables as follows:
SELECT * FROM Formulas, Context
This would result in M * N
rows.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With