Use table name in MySQL SELECT "AS"

Question

I use the table name to build a nested array when I evaluate my MySQL SELECT JOIN result in PHP. However, when working with AS for calculated columns, I can't seem to set the table name as intended (to simplify, I am trying without a JOIN in this example, but the problem is the same):

SELECT `field1`, `field2`, "Test" AS `Table`.`field3` FROM `Test`;

This throws an error.

How can I indicate in the SQL, which table I want to associate field3 with?

(resulting in the table name in $pdoStatement->getColumnMeta() being "Table")

Answer 1

To declare a string literal as an output column, leave the Table off and just use Test. It doesn't need to be associated with a table among your joins, since it will be accessed only by its column alias. When using a metadata function like getColumnMeta(), the table name will be an empty string because it isn't associated with a table.

SELECT
  `field1`, 
  `field2`, 
  'Test' AS `field3` 
FROM `Test`;

Note: I'm using single quotes above. MySQL is usually configured to honor double quotes for strings, but single quotes are more widely portable among RDBMS.

If you must have a table alias name with the literal value, you need to wrap it in a subquery with the same name as the table you want to use:

SELECT
  field1,
  field2,
  field3
FROM 
  /* subquery wraps all fields to put the literal inside a table */
  (SELECT field1, field2, 'Test' AS field3 FROM Test) AS Test

Now field3 will come in the output as Test.field3.

Answer 2

SELECT field1, field2, 'Test' AS field3 FROM Test; // replace with simple quote '