Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to join two unrelated tables in sql

Tags:

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] 
like image 895
fenix2222 Avatar asked Oct 04 '12 01:10

fenix2222


People also ask

How do you join two tables without a relationship?

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.

How do I combine two tables in SQL?

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.

Can we join two different tables in SQL?

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.


2 Answers

You want to use a CROSS JOIN:

SELECT FormulaId, Formula, ContextId, [Name] FROM Formula CROSS JOIN Context 
like image 132
LittleBobbyTables - Au Revoir Avatar answered Oct 26 '22 14:10

LittleBobbyTables - Au Revoir


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

SELECT * FROM Formulas, Context 

This would result in M * N rows.

like image 27
Vikdor Avatar answered Oct 26 '22 16:10

Vikdor