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:
|
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:
|
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!
Read Other SQL Guru Questions |