The SQL Guru Answers your Questions...
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!
Answered by SQLGuru graz - SQLTeam.com
