Merging 2 result sets of colums of an mysql query into one

Question

Example:

First Query:

select A,B,C from tb1;

---------------
A     B     C 
---------------
1     1     3
2     1     4

Second Query:

select E from tb2;

---------------
E     
---------------
8    
9

The required result format should be like this:-

-----------------------
A     B     C     E
-----------------------
1     1     3     8
2     1     4     9

Please tell me the query how to get the result set.

Think that their is no relation between two tables, just we need to append the two result set columns into one result set. — Bharath, Mar 30 '13 at 11:26
If there's no relation, probably join 2 subqueries by [a generated row number](http://stackoverflow.com/questions/3126972/mysql-row-number), but it seems likely just doing 2 queries & combining the results in your application is more effective. — Wrikken, Mar 30 '13 at 11:27

score 0 · Answer 1 · answered Mar 30 '13 at 12:08

to make your tables better structured if you can add in both tables a column id to identify the row number of each table.

then you can easily merge two table which have same id . the relation here is the id column.

then you could do this

 select A, B, C , E from Table1 t1
 inner join Table2 t2
 on  t1.id = t2.id

please see structured tables in this DEMO HERE

THE RESULT:

    A   B   C   E
    1   1   3   8
    2   1   4   9

hope it helps you !

score 0 · Answer 2 · answered Mar 30 '13 at 12:13

SQL tables are inherently unordered. This poses a problem, because there is nothing in your table to specify the ordering of the rows, which seems to be the connection between the two tables.

So, you have to hold your breath and make some assumptions. The particular assumption is that the data returned by a select is the actual order you want in the table (it could be different if you have deletes in the table or are running in a parallel environment).

The following code adds an id to each table. This id can then be used for the join:

select t1.A, t1.B, t1.C, t2.D
from (select t1.*, @rn := @rn + 1 as id
      from tbl1 t1 cross join (select @rn := 0)
     ) t1 left outer join
     (select t2.*, @rn := @rn + 1 as id
      from tbl2 t2 cross join (select @rn := 0)
     ) t2
     on t1.id = t2.id

score 0 · Answer 3 · answered Mar 30 '13 at 12:50

0

Hi may be it's help for you

you can try like this

SELECT * FROM tb1,tb2

And you get output like

-----------------------
A     B     C     E
-----------------------
1     1     3     8
2     1     4     9

answered Mar 30 '13 at 12:50

realy ??, try it yourself in sqlfiddle – echo_Me Mar 30 '13 at 12:59
yes , i tested in my local and i got all data from both table. – Mar 30 '13 at 13:00
yes i tested it some record got second time , so what you want ? – Mar 30 '13 at 13:26

Merging 2 result sets of colums of an mysql query into one

3 Answers3