how to combine two tables having different column name

Question

I have two tables say A and B .

table A:

+------------+
| manager_id |
+------------+
|        100 |
|        102 |
|        103 |
|        124 |
|        149 |
|        101 |
|        201 |
|        205 |
+------------+

Table:B

+------------+
| first_name |
+------------+
| Steven     |
| Lex        |
| Alexander  |
| Kevin      |
| Eleni      |
| Neena      |
| Michael    |
| Shelley    |
+------------+

i want to combine both the tables.the output tables should look like ..

the 1st row of table a should be combine with 1st row of table b. the 2nd row of table a should be combine with 2nd row of table b.and so on.....

OUTPUT:

+------------+-------------+
| manager_id | first_name  |
+------------+-------------+
|        100 |      steven |
|        101 |        lex  |
|        102 |   alexander |
|        103 |       kevin |
|        124 |       eleni |
|        149 |       neena |
|        201 |     michael |
|        205 |     shelley |
+------------+-------------+

I have tried different joins and union but not getting the required output.thanks in advance

note:these tables are the results of a query,so cant add any extra column.

You need to add a relation, i.e. An ID in Table B that corresponds with the manager_ID in Table A — Matt, Apr 22 '14 at 11:25
these tables are the results of a query,so cant add any extra column.@Matt — user2623213, Apr 22 '14 at 11:26
But there is an undocumented relationship e.g. manager_id '100' is for steven? — Matt, Apr 22 '14 at 11:27

score 4 · Answer 1 · answered Apr 22 '14 at 11:28

This is a very bad idea for a simple reason: SQL tables (including MySQL tables) are not ordered. So there is no guarantee that the results from select * are going to be in any particular order. There is no first row, second row, and so on -- at least there is no guarantee.

That said, sometimes you are faced with multiple tables loaded without auto-incremented ids and you can solve it. I highly recommend you validate the results afterwards, because there is no guarantee. You need to use variables for this:

select a.rn, a.manager_id, b.first_name
from (select @rn_a := @rn_a + 1 as rn, a.*
      from a cross join
           (select @rn_a := 0) const
     ) a join
     (select @rn_b := @rn_b + 1 as rn, b.*
      from b cross join
           (select @rn_b := 0) const
     ) b
     on a.rn = b.rn;

I want to add this this approach will not work in most databases, which support parallel processing of simple selects. For instance, Oracle and SQL Server will definitely not return large'ish table in insert order (unless you fiddle with a bunch of options). MySQL does seem to return tables in insert order (assuming no intermediate updates or deletes), although it is not guaranteed to do so.

how to combine two tables having different column name

1 Answers1