I have 2 tables, first one is material_table:
| id | material |
|---|---|
| 1 | steel |
| 2 | aluminum |
| 3 | copper |
| 4 | nickel |
Second one is dimension_table:
| width | length |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
I want to join them so I have something like result_table:
| material | width | length |
|---|---|---|
| steel | 1 | 1 |
| steel | 1 | 2 |
| steel | 1 | 3 |
I tried the following:
SELECT material_table.type, dimension_table.width, dimension_table.length
FROM dimensions_table
CROSS JOIN material_table;
But I get a resulting table like:
| material | width | length |
|---|---|---|
| steel | 1 | 1 |
| copper | 1 | 1 |
| nickel | 1 | 1 |
| steel | 1 | 2 |
| copper | 1 | 2 |
| nickel | 1 | 2 |
So to clarify I want to join my material table with the dimensions table, but the order of the columns is not the way I want it.
CodePudding user response：
You need to order every field that should be ordered. To be guaranteed to have the same output as expected, it would be:
SELECT material_table.type, dimension_table.width, dimension_table.length
FROM dimensions_table
CROSS JOIN material_table
ORDER BY material_table.type, dimension_table.width, dimension_table.length;
CodePudding user response：
SELECT material_table.type, dimension_table.width, dimension_table.length
FROM dimensions_table
CROSS JOIN material_table
ORDER BY material_table.type