Using a Union Statement in a View

From NeoWiki

The SQL UNION command allows you to display records from two different tables or queries as though they were in one table. The UNION Command can only be used in SQL Direct mode.

Views are similar to Queries, but with several differences:

• Not all database connections allow for Views
• Once created, Views cannot be edited, you must start over with a new View if you need to alter it.
• Views show up under that Tables section of the Main Database window and in many ways are treated as Tables.

Using UNION to Allow for Multiple OR Statements

Suppose we want to find all sightings where a female or a juvenile has been identified. Due to an known issue with multiple OR statements, our "query" will run faster if we use a View and a UNION command. Furthermore, if we wanted to group these results by Species, we would have to do it this way, as a grouping query based on a query containing multiple OR statements will cause NeoOffice to hang.

While someone familiar with SQL can build the entire View in SQL view, we will build the initial part of the View by using the View designer, and then shift to SQL view and SQL direct mode. Note that with SQL direct mode chosen, it is not possible to return to the View Design mode.

1. In the main Database window, click on the Tables icon in the left column.
2. Click on Create View
3. In the Add Table or Query window, click add the following tables by selecting them and clicking on the Add button.

   AOU_Birdlist

   Field_Trips

   Sightings

4. Close the Add Table or Query window
5. Add the following fields to the View designer table at the bottom of the View Design window. See Using a Query to Combine Fields From Different Tables if you are not sure how to do this.

   AOU_ID (from AOU_Birdlist)

   English_Name (from AOU_Birdlist)

   Female (from Sightings)

   Juvenile (from Sightings)

   Date (from Field_Trips)

6. In the Criterion row under Female, type TRUE
7. In the first Or row under Juvenile, type TRUE
8. Click on the Switch Design View On/Off button on the toolbar (the datasources icon with a blue drafting triangle on it). The view will shift to SQL view. and you will see and SQL statement that looks something like this:

   SELECT "AOU_Birdlist"."AOU_ID", "AOU Birdlist"."EnglishName", "Sightings"."Female", "Sightings"."Juvenile", "Field_Trips"."Date" FROM "Sightings", "AOU Birdlist", "Field_Trips" WHERE ( "Sightings"."Bird Name" = "AOU Birdlist"."EnglishName" AND "Sightings"."Field Trip ID" = "Field Trips"."ID" ) AND ( ( "Sightings"."Female" = True ) OR ( "Sightings"."Juvenile" = True ) )

9. Copy the entire SQL Statement
10. Click after the existing SQL Statement and hit Return a couple of times. (This is just to make it easier to read the SQL statement).
11. Type the word UNION and hit Return twice more
12. Now paste the copied SELECT, so you have two identical SELECT statements, one before and one after the UNION command.
13. Go back to the first SELECT statement, and find the section that says:

    AND ( ( "Sightings"."Female" = True ) OR ( "Sightings"."Juvenile" = True ) )

14. Edit it to read as follows:

    AND ( "Sightings"."Female" = True )

15. Go to the second SELECT statement, find the same section, and edit it to read as follows:

    AND ( "Sightings"."Juvenile" = True )

16. Click on the Run SQL command directly button on the toolbar (the single datasource icon with a green arrow pointing down).
17. Click on the Run Query button on the toolbar (the double datasource icon with a green arrow pointing down)
18. Verify that the query/view ran correctly
19. Save the View by clicking on the Save button on the toolbar. Give it a name like Female_Or_Juv
20. Close the View.

Using Union with Unrelated Tables

The example above uses the same Tables on either side of the UNION statement. But you can also use the UNION statement to combine records for unrelated Tables or Queries.