How to get a query to display table names from multiple tables

Question

I have a database with multiple tables all having similar information and I want ot get them into a single query.

How do I add the table name as a field in the SQL query?

I.E table1 has fields 'Name', 'type', 'description' etc, table2 has the same fields. I need a query which gives query1 'table', 'Name', 'type', 'description' etc when entry one says table=table1, name=name1, etc then line 50 (depending on 49 entries in table1) says table=table2, name=name from line 1 in table2 etc concatenating all the tables into one query with the table names as a full column.

Answer 1

SELECT 'table1' AS "table", Name, type, description
FROM  table1
UNION ALL
SELECT 'table2' AS "table", Name, type, description
FROM  table2
UNION ALL
...

Answer 2

SELECT "Table1" AS Table, Name, Type, Description

FROM  Table1

UNION ALL

SELECT "Table2", Name, Type, Description

FROM  Table2

UNION ALL

….and so on

Note that the column heading Table does not need to be delimited with quotes characters, only the constants "Table1", "Table2" etc.  Nor does the column heading need to be repeated in the second and subsequent parts of the UNION ALL operation. 

A UNION ALL operator is used here rather than a UNION operator because the latter suppresses any duplicate rows in the result table.  In this case this is unnecessary, so the more efficient UNION ALL operator is used. 

If you want to order the result table this should be done by means of an ORDER BY clause at the end of the query, e.g.

ORDER BY Table,Type; 

I would comment that the fact that you need to do this does suggest that data is being encoded as table names.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.  Normally a single table would be used, with a column with values distinguishing whatever attribute is currently being represented by the table names.  There would then be no need for the UNION ALL operation.  Subsets of the rows corresponding to your current tables would be returned by a query.

Answer 3

Full story:

Use a union query to combine multiple queries into a single result