Mysql "paste" Two Results Together (side By Side)

I want something like paste command in unix, it takes two files and prints first line, of first file, then separator, then first line from second file, then newline, then second li

Solution 1:

createtable if notexists first_40482804 (
    bla varchar(50)
) ;

createtable if notexists second_40482804 (
    cla int
) ;

truncatetable first_40482804 ;
truncatetable second_40482804 ;

insertinto first_40482804 ( bla ) values ('a') ;
insertinto first_40482804 ( bla ) values ('z') ;
insertinto first_40482804 ( bla ) values ('f') ;

insertinto second_40482804 ( cla ) values ( 80 ) ;
insertinto second_40482804 ( cla ) values ( 7 ) ;
insertinto second_40482804 ( cla ) values ( 15 ) ;

set@blaRow=0 ;
set@claRow=0 ;

select concat( `first`.bla,',',`second`.cla) paste from
(   
    select@blaRow:=@blaRow+1 `row` , bla from first_40482804
) `first`
leftjoin 
(
    select@claRow:=@claRow+1 `row` , cla from second_40482804
) `second`
on `first`.`row` = `second`.`row`

-- results

paste
a,80
z,7
f,15

Solution 2:

createtable if notexists first_40482804 (
    bla varchar(50)
) ;

createtable if notexists second_40482804 (
    cla int
) ;

truncatetable first_40482804 ;
truncatetable second_40482804 ;

insertinto first_40482804 ( bla ) values ('a') ;
insertinto first_40482804 ( bla ) values ('z') ;
insertinto first_40482804 ( bla ) values ('f') ;

insertinto second_40482804 ( cla ) values ( 80 ) ;
insertinto second_40482804 ( cla ) values ( 7 ) ;
insertinto second_40482804 ( cla ) values ( 15 ) ;

DELIMITER ;;

DROPPROCEDURE IF EXISTS get_paste_stored_procedure ;;

CREATE DEFINER=`root`@`localhost` PROCEDURE get_paste_stored_procedure()
BEGINset@blaRow=0 ;
    set@claRow=0 ;

    select concat( `first`.bla,',',`second`.cla) paste from
    (   
        select@blaRow:=@blaRow+1 `row` , bla from first_40482804
    ) `first`
    leftjoin 
    (
        select@claRow:=@claRow+1 `row` , cla from second_40482804
    ) `second`
    on `first`.`row` = `second`.`row` ;

END;;
DELIMITER ;

call get_paste_stored_procedure() ;

Solution 3:

There's no ROWNUMBER facility in MYSQL but you can mimick it like this :

SELECT t.*, 
       @rownum :=@rownum+1AS rank
  FROM YOUR_TABLE t, 
       (SELECT@rownum :=0) r

So you can make 2 queries returning the row number for each table:

SELECT bla, @rownum :=@rownum+1AS rank
  FROM table_1 , (SELECT@rownum :=0) r1


SELECT bla, @rownum :=@rownum+1AS rank
  FROM table_2 , (SELECT@rownum :=0) r2     

And join them together on the row number

SELECT
    CONCAT( T1.bla,',',T2.cla) 
FROM
    (   SELECT bla, @rownum :=@rownum+1AS rank
          FROM table_1 , (SELECT@rownum :=0) r1
    ) T1
INNERJOIN
    (       SELECT cla, @rownum :=@rownum+1AS rank
      FROM table_2 , (SELECT@rownum :=0) r2     
    ) T2
    ON T1.rank = T2.rank

Of course with that INNER join to get the results your are expecting, I have guessed that you have the same number of rows in both tables, otherwise this has no sense to me.

---

Edit: I am a bit late as keith posted more or less the same approach