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. A school for example, might have separate tables for students and faculty, but might want to combine the records from these tables to make a phone directory.

In our example, say we take a visit to Europe or northern Asia and go birdwatching there. Not all of those birds will be on the AOU birdlist, which lists birds in North America. So we create a new set of tables and forms in the same database file but specifically designed to track paleartic birds. We'd still like to see a life list including birds from both the AOU and Paleartic lists. A UNION command will let us do that.

Preparing the SELECT statements

A UNION statement consists of two SELECT statements (the Query designer lets you build a SELECT statement without knowing SQL) joined by the UNION command. In our case, one SELECT statement will select data about birds seen in the AOU birding area and the other will select data about birds seen in the paleartic birding area. The field names in the two statements do not have to have to be the same, but the following rules must be followed:

• Each statement must include the same number of fields
• The parallel fields must be in the same order in both statements
• The parallel fields must be of the same or very similar type. (i.e. a text field and a date field cannot be parallel fields.)

Those who are very familiar with SQL can create these SELECT statements "from scratch," but for the rest of us, it is easier to use the Query Design view to create our SELECT statements for us. Here's how to do so:

1. Create a new Query in Design view, and set up the fields you want to use. If you are going to use the View in a grouping query, make sure not to specify a sort order.
2. Shift to SQL view by clicking the Switch Design View On/Off button on the toolbar. My query looked like this:

   SELECT "AOU_Birdlist"."ID", "Sightings"."Bird Name", "Field_Trips"."Date", "Locations"."LocationName", "Locations"."Country" FROM "Sightings", "AOU Birdlist", "Field_Trips", "Locations" WHERE ( "Sightings"."Bird_Name" = "AOU_Birdlist"."EnglishName" AND "Sightings"."Field_Trip ID" = "Field_Trips"."ID" AND "Field Trips"."Location" = "Locations"."LocationName" )

3. Copy the SQL statement and paste it in an easily accessible location (like a blank Writer document)
4. Create another Query in Design view for the second select statement and copy and paste it as before. My second SELECT statement looked like this:

   SELECT "zPaleartic_Birds"."PA_ID", "zPaleartic_Sightings"."Bird_Name", "zPaleartic_Field_Trips"."Date", "zPaleartic_Locations"."LocationName", "zPaleartic_Locations"."Country" FROM "zPaleartic_Field_Trips", "zPaleartic_Locations", "zPaleartic_Sightings", "zPaleartic_Birds" WHERE ( "zPaleartic_Field_Trips"."Location" = "zPaleartic_Locations"."LocationName" AND "zPaleartic_Sightings"."Bird_Name" = "zPaleartic_Birds"."English_Name" AND "zPaleartic_Sightings"."PA_FT_ID" = "zPaleartic_Field_Trips"."PA_Trip_ID" )

5. Create a new View by clicking on the Tables icon in the Databases column of the main document window and then clicking on the Create View icon in the Tasks section.
6. Close the Add Table or Query window.
7. Click on the Switch Design View On/Off button on the toolbar to shift to SQL view
8. Click on the Run SQL command directly button on the toolbar
9. Paste the first SELECT statement in the workspace of the View Design window
10. Click after the statement and hit the Return key twice
11. Type UNION
12. Hit the Return key twice
13. Paste the second SELECT statement after the blank returns.
14. Click on the Run Query button to see if it works correctly
15. Save and close the view.

In our example, this process would create a list of all the sightings of all birds in both regions. To create a life list, a Grouping Query would need to be created, based on this view.