When querying multiple tables using UNION ALL the AS keyword only works for the first table

mysql, sql

Solution

Your query returns a single column. A single column can only have one name/alias. In a UNION query, the first subquery defines the resulting set's column names.

If you want to specify which table each value has come from, add another column, e.g. like this:

(SELECT col1, 'table1' AS src FROM table1 WHERE col3 IS NOT NULL)
UNION ALL
(SELECT col1, 'table2'        FROM table2 WHERE col3 IS NOT NULL)
UNION ALL
(SELECT col1, 'table3'        FROM table3 WHERE col3 IS NOT NULL)

Problem

I have a query: ``` (SELECT col1 AS table1 FROM table1 WHERE col3 IS NOT NULL) UNION ALL (SELECT col1 AS table2 FROM table2 WHERE col3 IS NOT NULL) UNION ALL (SELECT col1 AS table3 FROM table3 WHERE col3 IS NOT NULL) ``` However when I process this using PDO and the `fetchAll(PDO::FETCH_ASSOC);` command, the keys to the array generated all come out as table1 irrespective of the table they are actually from. Is my syntax incorrect? Thanks!

Original source