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- gt getColumnMeta   being  Table

User · Answer

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.

User · Answer

SELECT field1  field2   Test  AS field3 FROM Test     replace with simple quote

[mysql] Use table name in MySQL SELECT "AS"

Examples related to mysql