Why Does My UNION Query Cause a 'Query Input Must Contain at Least One Table or Query' Error?

UNION query error in Jet/ACE database engine: missing table or query

Question:

You may encounter the error "Query input must contain at least one table or query" when executing a UNION query that does not contain a table source.

For example, the following query is valid:

<code class="language-sql">SELECT "Mike" AS FName</code>

However, trying to join multiple rows using the following query fails:

<code class="language-sql">SELECT "Mike" AS FName
UNION ALL
SELECT "John" AS FName</code>

Solution:

This is not a bug or limitation of the Jet/ACE database engine. To perform a UNION or UNION ALL operation on multiple rows, you must include the FROM clause, even if you are not referencing any fields in the data source.

Example:

You can use a table with only one row as a virtual table, for example:

<code class="language-vba">Public Sub CreateDualTable()
    Dim strSql As String
    strSql = "CREATE TABLE Dual (id COUNTER CONSTRAINT pkey PRIMARY KEY);"
    CurrentProject.Connection.Execute strSql
End Sub</code>

You can then use this table in a UNION query:

<code class="language-sql">SELECT "Mike" AS FName
FROM Dual
UNION ALL
SELECT "John" AS FName
FROM Dual;</code>

Alternative:

Alternatively, you can use a SELECT statement with a TOP 1 or WHERE clause to limit the result set to one row:

<code class="language-sql">SELECT TOP 1 "Mike" AS FName
FROM (SELECT "Mike" AS FName UNION ALL SELECT "John" AS FName) AS SubQuery

SELECT "Mike" AS FName
FROM (SELECT "Mike" AS FName UNION ALL SELECT "John" AS FName) AS SubQuery
WHERE ID = 1</code>

The above is the detailed content of Why Does My UNION Query Cause a 'Query Input Must Contain at Least One Table or Query' Error?. For more information, please follow other related articles on the PHP Chinese website!