(This page is still under construction)
Overview
Sometimes when writing a query, it is necessary to gather data from different areas of the database and join them into one query. An example of this might be a sweep of the student records for certain conditions and the application data for another condition. Other times, the user might want data to appear on different lines. Such a report might have student statistics (GPA, total credit, program, plan, etc.) on the first line, class enrollments on another line, and maybe some other data on a third line. Unions can help you produce such a report. It is similar concept as join but it is not join, an union helps to eliminate duplication of data in a report.
What is an Union
Basically, a union is two or more queries run as one. Unions have their own rules which govern their creation. The top query in the union is the boss. It determines how many fields a query should have - if the top query has fifteen fields, then the other queries in the union must have fifteen fields, even if those fields are empty. The top query determines the sort order and the headings for the columns. The top query determines the data type of the column. If column 5 in the top query is a number, then column 5 in the other queries must be a number. Numbers go with numbers, and dates or characters may go with a date or character. Also, in unions, the ability to use the translation table descriptions is lost for all queries in the unions.
Illustration of an Union
Our first example is of a report of student enrollment. The first query (first line) gathers statistics for students who are currently enrolled. It would pull students who are active in a program which belongs to the prompted INSTITUTION. Then it might gather the plan (or multiple plans), get the name, phone number and email, and the credit and GPA information for the students who are term activated for the prompted term. The second query will basically pulls similar data but add enrollment to the picture. The second query will build the second line. This would allow the report to repeat only certain data, such as the emplid, while blanking out redundant data such as name, program or plan(s). And the second line would include data which does not appear on the first line, such as subject, catalog# and section.
