When doing a union on two databases where I create one big recordset using ASP, how do I then refer to the fields in the two databases if both databases are the same, but have different field names?
To start, I'm going to assume you meant to say tables instead of databases. The UNION operator works only on tables. If the tables exist in separate databases, then I believe you just qualify them, e.g. db1.tableA and db2.tableB.
The question you have is interesting, though, and the answer is that the result set assumes its column names from the first query. Consider the following example:
select albumname, artist, label from CDs where favourite = 'Y' union all select booktitle, author, publisher from books where choice = 'Y'
The result set will have columns named albumname, artist, and label. Often, this does not matter, since results sets are usually displayed by applications which provide meaningful column headings instead of using column names. For a query that combines CDs and books, the application would, one hopes, use column headings like "Name or title," "Artist or author," and "Label or publisher," while underneath the covers, so to speak, it would just refer to the column names of the first query.
Still, if you wish to change the column names, you can provide a column alias. This is especially important when the first query's select list contains expressions in addition to columns:
select label as source , count(*) as favourites from CDs where favourite = 'Y' group by label union all select publisher , count(*) from books where choice = 'Y' group by publisher
Finally, you asked "how do I then refer to the fields on the 2 databases?" and perhaps you have a need to distinguish which rows in the result set came from which table in the union. This requirement comes up fairly often, and the answer is simply to include an extra column in each query:
select 'CDs: ' as sourcetype , label as source , count(*) as favourites from CDs where favourite = 'Y' group by label union all select 'Books: ' , publisher , count(*) from books where choice = 'Y' group by publisher
Notice that each GROUP BY clause does not need to group on the first column, because it's the same for the entire (sub)set of results created by each query. Therefore, with no further logic required, it is obvious that this example should use UNION ALL instead of UNION, to avoid having the database sort all combined result rows looking for duplicate rows, since between the queries there cannot be any duplicates because of that first column. Without that first column, there could be duplicates between the queries, but ordinarily you'd want to use UNION ALL anyway, simply because in most unions, you really do want to see "duplicate" rows if they come from separate queries.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.