MySQL UNION print every other

How can I make a sql query like the following:

(SELECT `name` FROM table1)
UNION
(SELECT `name` FROM table2)

return each other. Ie. name1 from table1, name1 from table2, name2 from table1, name2 from table2 and so on?

Edit: Table1

name
Hello
world

Table2

name
g开发者_如何转开发uten
tag

The output wanted:

Hello
guten
world
tag

and this should also be possible if adding more unions, so that it takes from the first union, then the second, third, forth and so on.

You could number rows with variables, using 2,4,6,... for the first part of the union and 3,5,7,... for the second:

select @rownum1 := @rownum1+2 as rownum, name
from (select @rownum1:=0) r, table1
union all
select @rownum2 := @rownum2+2 as rownum, name
from (select @rownum2:=1) r, table2
order by rownum;

The select in the from clause (select @rownum2:=1) r is only used to initialize the variable.

Does this work?

set @i = 0;
set @j = 1;

select  @i:=@i+2 as rownumber,
        name
from    table1

union

select  @j:=@j+2 as rownumber,
        name
from    table2

order by rownumber

I read your question as wanting to alternate one row from table1, one from table2 and so on in your results set.

Edit in light of your edit:

Change the "2"s to the number of tables, and add as many variables as you have tables, with consecutive start values in the "set" statements. This will extend in the way you want.

In a similar vein to David M, you can do:

(SELECT @rownum:=@rownum+1 as rownum,`name` FROM table1, (SELECT @rownum:=0) r)
UNION
(SELECT @rownum:=@rownum+1 as rownum,`name` FROM table2, (SELECT @rownum:=0) r)
order by rownum