Union Queries or Joining Queries Together
Imagine you have three tables
containing similar data. You want all the data from those three tables in one
query. Creating one record for each entry in each table. So if table1 had 50
records table2 had 20 records and table3 had 10 records the resulting query
would have 80 records in it.
Here is the example database union.mdb
and zipped union.zip
Below is another example.
Table 1
| PayrollNo | Name |
|---|
| 1123 | John
| | 1456 | Steve
| | 1567 | Kevin
| | 2356 | James
|
Table 2
| PayrollNo | Name |
|---|
| 1478 | Contractor
| | 1123 | Temp
|
Table 3
| PayrollNo | Work Done |
|---|
| 1678 | 1/5/99
| | 1854 | 3/4/99
| | 1123 | 1/1/98
|
From the three tables you want to select all
payroll numbers.
Query Results
| PayrollNo
|
|---|
| 1123 | | 1456 | | 1567 | | 2356 | | 1478 | | 1123 | | 1678 | | 1854 | | 1123
|
Note that 1123 is listed three times. This is because it appears once in table1,
once
in table2 and once in table3.
This type of query is called a UNION QUERY
and is not the same to design as other queries.
Design a new query. Don't bother to bring the
table names in as you can't use them. Click on Query menu and choose SQL
Specific and then choose Union
That should change the query screen to a
blank text box. If you haven't seen this view before it's called the SQL view of
queries and it's where really sad propeller heads write their queries rather
that using the intuitive interface MS gave you. Unfortunately Microsoft decided
not to give you an interface to create SQL specific queries, so you have to type
it in yourself.
For the above example the SQL to be typed
would be
SELECT [PayrollNo]
FROM [Table1]
UNION ALL SELECT [PayrollNo]
FROM [Table2];
UNION ALL SELECT [PayrollNo]
FROM [Table3]; |
What that means in English is. Select all PayrollNo from table1 add to it all
PayrollNo in table 2 and add to that all PayrollNo in table3.
If we left
out the ALL word in the second two statements 1123 would only have been returned
once. See below
SELECT [PayrollNo]
FROM [Table1]
UNION SELECT [PayrollNo]
FROM [Table2]; |
Query Results (Without ALL statement)
| PayrollNo
|
|---|
| 1123 | | 1456 | | 1567 | | 2356 | | 1478 | | 1678 | | 1854
|
You can also return more that one field by seperating
field names with a comma on the select statement. All fields must be of the same type and specified in the same order.
SELECT [PayrollNo],[Name]
FROM [Table1]
UNION SELECT [PayrollNo],[Name]
FROM [Table2]; |
Query Results (With names)
| PayrollNo | Name
|
|---|
| 1123 | John
| | 1456 | Steve
| | 1567 | Kevin
| | 2356 | James
| | 1478 | Contractor
| | 1123 | Temp
|
Note that 1123 is listed twice now. That's because the names weren't unique. One is John the other is temp.
This time we are going to specify a criteria. We want all
records whose payrollno is greater than 1400.
SELECT [PayrollNo],[Name]
FROM [Table1]
WHERE [PayrollNo] > 1400
UNION SELECT [PayrollNo],[Name]
FROM [Table2]
WHERE [PayrollNo] > 1400; |
Note we have to specify the criteria twice. Once for each table. You can specify different criteria for each table if you want.
Query Results (Specifc criteria)
| PayrollNo | Name
|
|---|
| 1456 | Steve
| | 1567 | Kevin
| | 2356 | James
| | 1478 | Contractor
|
Finally you can join different fields together if necessary using the as clause
SELECT [PayrollNo],[Name]
FROM [Table1]
UNION SELECT [PayrollNo],cstr([Work Done]) AS 'Name'
FROM [Table2]
|
Note we have to convert the date into a string. Obviously the example given is not a useful one but knowing this trick you will soon find a use for it.
I use this trick all the time to convert flat data imported into a database into relational data without having to write a single line of code.
| 