MySQL combine columns of multiple tables

Question

I have two tables like indicated below: Table1:

name    surname    age
----    -------    ---
 a         b       null
 c         d       null

Table 2:

age
---
 1 
 4

I want to combine them into first table (Table1) like

name    surname    age
----    -------    ---
 a         b        1
 c         d        4

(they have the same number of rows) How can I combine them?

How are you connecting the users in Table 1 with the age in Table 2? Or are you trying to do by row number? — rae1
– rae1, Commented Dec 29, 2012 at 8:11
@JoshuaJeanThree bad design, you should recreate it by giving primary keys. — John Woo
– John Woo, Commented Dec 29, 2012 at 8:17

John Woo · Accepted Answer · 2012-12-29 08:13:09Z

7

maybe you want this,

SELECT a.Name, a.SurName, b.Age
FROM
  (
    SELECT @row := @row + 1 RankNo,
           Name, Surname
    FROM   Table1, (SELECT @row := 0) r
  ) a
  INNER JOIN
  (
    SELECT @row1 := @row1 + 1 RankNo,
           Age
    FROM   Table2, (SELECT @row1 := 0) r
  ) b ON a.RankNo = b.RankNo

SQLFiddle Demo

answered Dec 29, 2012 at 8:13

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

4 Comments

fthiella Over a year ago

how can we be sure that rows will always be returned in the same order? theoretically mysql could return rows with any order, unless you specify an order by

John Woo Over a year ago

@fthiella mysql could return rows with any order. nope. it depends on which field you have set an index first.

fthiella Over a year ago

without knowing if there's an index or not, rows order can't be determined. But I am also not sure that an index would solve this problem, of course MySql would use an index to return rows and to sort rows, but I am not sure if that's guaranteed or documented somewhere.

fthiella Over a year ago

pls see here: dev.mysql.com/doc/refman/5.5/en/innodb-index-types.html it looks like that, when no explicit index is given, default fifo order is always guaranteed for InnoDB tables. So your answer should be correct!

Collectives™ on Stack Overflow

MySQL combine columns of multiple tables

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related