MySQL UNION print every other

MySQLsql

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
guten
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.

Best Answer

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.

UNION Example:

SELECT 'foo' AS bar UNION SELECT 'foo' AS bar

Result:

+-----+
| bar |
+-----+
| foo |
+-----+
1 row in set (0.00 sec)

UNION ALL example:

SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar

Result:

+-----+
| bar |
+-----+
| foo |
| foo |
+-----+
2 rows in set (0.00 sec)

Mysql – concatenate multiple MySQL rows into one field

You can use GROUP_CONCAT:

SELECT person_id,
   GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Ludwig stated in his comment, you can add the DISTINCT operator to avoid duplicates:

SELECT person_id,
   GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY:

SELECT person_id, 
       GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:

SET group_concat_max_len = 2048;

Of course, you can change 2048 according to your needs. To calculate and assign the value:

SET group_concat_max_len = CAST(
                     (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
                           FROM peoples_hobbies
                           GROUP BY person_id) AS UNSIGNED);

Best Answer

Related Solutions

Sql – the difference between UNION and UNION ALL

UNION Example:

UNION ALL example:

Mysql – concatenate multiple MySQL rows into one field

Related Topic