Use table name in MySQL SELECT "AS"

Question

I use the table name to build a nested array when I evaluate the MySQL SELECT JOIN result in PHP. However, when working with AS for calculated columns, I cannot specify the table name as intended (to simplify, I try without JOIN in this example, but the problem is the same):

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

This causes an error.

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

(as a result, the table name in $pdoStatement->getColumnMeta() will be "Table")

Answer 1

To declare a string literal as an output column, leave Table turned off and just use Test . This does not need to be associated with a table between your joins, since only its column alias will be available to it. When using a metadata function such as getColumnMeta() , the table name will be an empty string because it is not associated with the table.

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

Note. I use single quotes above. Normally, MySQL is configured to respect double quotes for strings, but single quotes are more widely portable among RDBMSs.

If you must have a table alias name with a 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 appear on the output as Test.field3 .

Answer 2

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