Using mysql aliases to select columns from 2 tables
Solution 1
It is not an alias problem that you have. You are performing a CROSS JOIN
on the table which creates a cartesian result set.
This multiplies your result set so every row from table_a
is matched directly to every row in table_b
.
If you want to JOIN
the tables together, then you need some column to join the tables on.
If you have a column to JOIN
on, then your query will be:
select a.open as a_open,
b.open as b_open
from table_a a
inner join table_b b
on a.yourCol = b.yourCol
If you do not have a column that can be used to join on, then you can create a user-defined variable to do this which will create a row number for each row.
select
a.open a_open,
b.open b_open
from
(
select open, a_row
from
(
select open,
@curRow := @curRow + 1 AS a_row
from table_a
cross join (SELECT @curRow := 0) c
) a
) a
inner join
(
select open, b_row
from
(
select open,
@curRow := @curRow + 1 AS b_row
from table_b
cross join (SELECT @curRow := 0) c
) b
) b
on a.a_row = b.b_row;
Solution 2
You need a column that may be used to join that two tables.
You can try generating a pseudo-column as a row number, but I'm not sure that it's what you're trying to achieve. This should look like that (can test it right now, but the idea is clear):
SELECT
a.open, b.open
FROM
(SELECT
open, @curRow := @curRow + 1 AS row_number
FROM
table_a
JOIN
(SELECT @curRow := 0)
) a
JOIN
(SELECT
open, @curRow := @curRow + 1 AS row_number
FROM
table_b
JOIN
(SELECT @curRow := 0)
) b
ON
a.row_number = b.row_number
rocketas
Updated on June 04, 2022Comments
-
rocketas almost 2 years
I have 2 tables: table_a and table_b. Both contain a column named 'open'.
table_a +-------+ | open | +-------+ | 36.99 | | 36.85 | | 36.40 | | 36.33 | | 36.33 | +-------+ table_b +------+ | open | +------+ | 4.27 | | 4.46 | | 4.38 | | 4.22 | | 4.18 | +------+
I'd like to write a query that returns the following
+-------++------+ | open || open | +-------++------+ | 36.99 || 4.27 | | 36.85 || 4.46 | | 36.40 || 4.38 | | 36.33 || 4.22 | | 36.33 || 4.18 | +-------++------+
I attempt the following query:
select a.open, b.open from table_a a, table_b b;
This returns a table with every value of table_b.open for each value of table_a.open
+-------++------+ | open || open | +-------++------+ | 36.99 || 4.27 | | 36.99 || 4.46 | | 36.99 || 4.38 | | 36.99 || 4.22 | | ... || 4.18 | + ... ++------+
I can see I'm misunderstanding proper usage of aliases here. Any ideas?
-
rocketas about 11 yearsAh I see. Is there a way around this?
-
Taryn about 11 years@holiday_cannibalism do you have another column that could be used to join on?
-
Taryn about 11 years@holiday_cannibalism See my edit, you can use a user defined variable to generate a value to join the tables on.
-
rocketas about 11 yearsI did indeed have another column(date) and using
-
MarcinJuraszek about 11 yearsSo show whole schema for both tables, and say us how you decide which rows should be combined together.
-
rocketas about 11 years'select a.open as open_a, b.open as open_b from table_a a, table_b b where a.date = b.date' yielded the correct result. Didn't know you could do that with the row number though. Thanks.