Writing UNION Queries and Subqueries

Writing UNION Queries and Subqueries

UNION queries and queries that include subqueries require you to write Jet SQL statements. Union is one of the three choices of Query Design view's Query, SQL Specific menu. There is no menu choice for subquery. The following sections provide general syntax examples for writing UNION and subqueries, and provide simple Jet SQL examples. The general syntax examples use the same format as those of Chapter 21, "Moving from Jet Queries to Transact-SQL," for T-SQL statements.

Using UNION Queries to Combine Multiple Result Sets

graphics/union_queries.gif UNION queries let you combine the result set of two or more SELECT queries into a single result set. Northwind.mdb includes an example of a UNION query, which has the special symbol of two overlapping circles, in the Database window. You can create UNION queries only with SQL statements; if you add the UNION keyword to a query, the Query Design Mode button on the toolbar and the query design choices of the View menu are disabled.

The general syntax of UNION queries is as follows:

 SELECT select_statement   UNION SELECT select_statement     [GROUP BY group_criteria]     [HAVING aggregate criteria]   [UNION SELECT select_statement     [GROUP BY group_criteria]     [HAVING aggregate criteria]]   [UNION. . .]   [ORDER BY column_criteria] 

The restrictions on statements that create UNION queries are the following:

  • The number of fields in the field_list of each SELECT and UNION SELECT query must be the same. You receive an error message if the number of fields is not the same.

  • The sequence of the field names in each field_list must correspond to similar entities. You don't receive an error message for dissimilar entities, but the result set is likely to be unfathomable. The field data types in a single column need not correspond; however, if the column of the result set contains both numeric and Jet Text data types, the data type of the column is set to Text.

  • Only one ORDER BY clause is allowed, and it must follow the last UNION SELECT statement. You can add GROUP BY and HAVING clauses to each SELECT and UNION SELECT statement if needed.

The sample Customers and Suppliers by City query is a UNION query that combines rows from the Customers and Suppliers tables. When you open a UNION query, Query Design view is disabled.

To create a new UNION query, click the Database window's New button, select Design view, close the Show Table dialog, choose Query, SQL Specific, Union query to open the SQL window, and type the SQL statement.

Jet SQL

graphics/sql.gif The Jet SQL statement for a slightly modified version of the Customers and Suppliers by City query is

 SELECT City, CompanyName, ContactName,   CustomerID AS Code, "Customer" AS Relationship FROM Customers UNION SELECT City, CompanyName, ContactName,    SupplierID, "Supplier" FROM Suppliers ORDER BY City, CompanyName; 

The syntax of the preceding SQL statement illustrates the capability of UNION queries to include values from two different field data types, Text (CustomerID) and Long Integer (SupplierID), in the single, aliased Code column (see Figure 11.73).

Figure 11.73. The Code column of this UNION query demonstrates Jet's capability to combine values of two different data types. T-SQL UNION queries require compatible data types in a column.

graphics/11fig73.jpg

You also can use UNION queries to add (All) or other explicit options to a query result set when populating combo and list boxes. As an example, the following SQL statement adds (All) to the query result set for a combo box used to select orders from a particular country or all countries:

 SELECT Country FROM Customers    UNION SELECT "(All)" FROM Customers ORDER BY Country; 

The parentheses around (All) causes it to sort at the beginning of the list; the ASCII value of "(" is 40 and "A" is 65. Automatic sorting of combo and list box items uses the ASCII value returned by the VBA Asc function.

To create a query that returns all rows from joined tables, see "Using the tblShipAddress Table in a Query," p. 506.


To update a table by substituting a string for a specified value, see "Using the tblShipAddress Table with UNION Queries," p. 508.


For examples of using a UNION query to add an (All) item to a drop-down list, see "Adding an Option to Select All Countries or Products," p. 1244.


Implementing Subqueries

Access traditionally has used nested queries to emulate the subquery capability of ANSI SQL, because early Jet versions didn't support subqueries. Access 2003 lets you write a SELECT query that uses another SELECT query to supply the criteria for the WHERE clause. Depending on the complexity of your query, using a subquery instead of nested queries often improves performance. The general syntax of subqueries is as follows:

 SELECT field_list   FROM table_list   WHERE [table_name.]field_name     IN (SELECT select_statement   [GROUP BY group_criteria]      [HAVING aggregate_criteria]   [ORDER BY sort_criteria]); 

Jet SQL

graphics/sql.gif Following the Jet SQL statement for a subquery that returns names and addresses of Northwind Traders customers who placed orders between January 1, 1997, and June 30, 1997:

 SELECT CompanyName, ContactName, ContactTitle, Phone FROM Customers WHERE CustomerID IN     (SELECT CustomerID FROM Orders     WHERE OrderDate BETWEEN #1/1/1997# AND #6/30/1997#); 

The SELECT subquery that begins after the IN predicate returns the CustomerID values from the Orders table against which the CustomerID values of the Customers table are compared. Be sure to surround the subquery with parentheses.

Unlike UNION queries, you can create a subquery in Query Design view. You type IN, followed by the SELECT statement as the criterion of the appropriate column, enclosing the SELECT statement within the parentheses required by the IN predicate. Figure 11.74 shows the query design with part of the IN (SELECT...) statement in the Criteria row of the Customer ID column. Figure 11.75 shows the result set returned by the SQL statement and the query design.

Figure 11.74. You can create the base query in Access's Query Design view, but you must type the IN predicate and the subquery's SELECT statement in the Criteria row of the grid.

graphics/11fig74.gif

Figure 11.75. This datasheet displays the result set of the subquery design of Figure 11.74.

graphics/11fig75.jpg



Special Edition Using Microsoft Office Access 2003
Special Edition Using Microsoft Office Access 2003
ISBN: 0789729520
EAN: 2147483647
Year: 2005
Pages: 417

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net