Today's question comes from Rick:

I am trying to design an ASP page that allows the user to input one or more parameters to search on. When they click on submit, I need to build a SQL statement that reflects their desired choices. The information they can search on, is spread across multiple tables!

Rick needs to pass one SQL statement to the server that will allow him to search multiple tables and combine the results into one record set for his ASP page. He could call the server multiple times, once for each query and then combine the results on the ASP page programatically. That is way too much work for a programmer though. Especially one as lazy as me! :-)

An easier way to this is with the UNION statement. It takes multiple select statements and combines them into one result set.

Let's look at an example using the pubs database. Let's suppose you want wanted to build an address book application that would search the AUTHORS table and the EMPLOYEE table based on last name. Your initial query would look something like this:

SELECT au_lname FROM authors UNION ALL SELECT lname FROM Employee ORDER BY au_lname

This will return all the records in the AUTHORS table and then all the records in the EMPLOLYEE table in one result set. The UNION will remove duplicates from the result set by default. You can use the UNION ALL to keep the duplicates in the result set.

The ORDER BY clause applies to the entire result. You can only have one ORDER BY clause in a UNION query and it must be part of the last SELECT statement. The first SELECT statement defines the column names. I usually try to alias all my columns to the same name for easier reading. It's probably also not a good idea to use SELECT * in a UNION query as tables structures change. Now let's make this a little more complicated:

SELECT id=convert(varchar(11), au_id), last_name = au_lname, table_name = 'authors' FROM authors Where au_lname = 'smith' UNION ALL SELECT id=convert(varchar(11), emp_id ), last_name = lname, table_name = 'employee' FROM Employee where lname = 'Smith' ORDER BY last_name

First we added a column I'm calling ID. We converted both AU_ID and EMP_ID to a consistent data type. SQL Server will convert them automatically if possible but I prefer to do it myself. I also added a column to identify where the record came from. With the ID and record type (table name) you should be able to build a pointer (HREF) on your ASP page. I also had to include the WHERE clause in each SQL statement. You can simplify that using a view but we'll leave that as an exercise for you readers.

You can string as many SELECT statements together as you'd like. The can search on different fields in each statement. Keep in mind that SQL Server will have to execute all those statements so you'll want to keep it brief. You can also use a GROUP BY in the individual SELECT statements. That's it for the UNION statement.

Happy Selecting!