Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-06-24 : 09:06:16
|
| Paul writes "How can I apply a WHERE clause to the results of this UNION query. I guess it would have to be a two step process by setting up a VIEW of the first SELECT UNION statement?SELECT ID, au_lname FROM authorsUNION ALLSELECT ID, lname FROM EmployeeIf I wanted to apply this to the results could I do it in the same above query? WHERE EXISTS (SELECT * FROM ApprovedID WHERE ApprovedID.ID = ResultsfromQuery.ID)" |
|
|
MakeYourDaddyProud
184 Posts |
Posted - 2002-06-24 : 09:13:27
|
| There are two ways but you are opening up a can of worms. You can bind the select statement into a derived table for post V6.5 T-SQL i.e.select * from (SELECT ID, au_lname FROM authors UNION ALL SELECT ID, lname FROM Employee ) ut1where ut1.col1 = value1and ut1.col2 = value2 blah blah blah......or create a view on the above and just apply the where clause in the normal fashion.BUT BUT BUT...Any indexes may be compromised as a result of you UNION. Please be sure to check the execution plan for any table scans and redesign your tables if easily possible. (But i wouldn't want to be on your payroll!!)SQL 2000 implements indexed views, read more on BOL on how you can optimise this situation for this feature if you are using 2000.Danno |
 |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-24 : 09:14:43
|
select *from ( SELECT ID, au_lname FROM authors UNION ALL SELECT ID, lname FROM Employee ) r inner join ApprovedID a on r.ID = a.ID Jonathan Boott, MCDBA |
|
|
|
|
|
|
Can I apply a WHERE clause to the results of a UNIONed table