Using mysql aliases to select columns from 2 tables

Question

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?

try this: select a.open as open_a, b.open as open_b from table_a a, table_b b; — MUG4N
– MUG4N, Commented Mar 3, 2013 at 20:52
still getting every value of table_b.open for each value of table_a.open. table_a.open seems to move on to its next value only after being printed next to every table_b.open value — rocketas
– rocketas, Commented Mar 3, 2013 at 20:55

Taryn · Accepted Answer · 2013-03-03 21:08:14Z

7

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;

See SQL Fiddle with Demo

edited Mar 3, 2013 at 21:08

answered Mar 3, 2013 at 20:54

Taryn

249k57 gold badges375 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

rocketas Over a year ago

Ah I see. Is there a way around this?

Taryn Over a year ago

@holiday_cannibalism do you have another column that could be used to join on?

Taryn Over a year ago

@holiday_cannibalism See my edit, you can use a user defined variable to generate a value to join the tables on.

MarcinJuraszek · Accepted Answer · 2013-03-03 20:59:10Z

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

answered Mar 3, 2013 at 20:59

MarcinJuraszek

126k15 gold badges200 silver badges267 bronze badges

3 Comments

rocketas Over a year ago

I did indeed have another column(date) and using

MarcinJuraszek Over a year ago

So show whole schema for both tables, and say us how you decide which rows should be combined together.

rocketas Over a year ago

'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.

Collectives™ on Stack Overflow

Using mysql aliases to select columns from 2 tables

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related