Wrox Programmer Forums
|
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 17th, 2009, 12:20 PM
Authorized User
 
Join Date: Apr 2009
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default Union Query Help

select supplier_id, supplier_name
from suppliers
where supplier_id > 2000
UNION
select company_id, company_name
from companies
where company_id > 1000

(and name = 'xyz' and last_name = 'xyz')

ORDER BY 2;

In above query I need to give some more and condition at the end for the whole result.
for e.g. above (and name = 'xyz' and last_name = 'xyz') condition should apply for the whole result instead of last union query. It is not working somehow for me....Can anyone please help me ?
 
Old June 17th, 2009, 03:23 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 166
Thanks: 2
Thanked 33 Times in 33 Posts
Default

Yes, the name clauses will apply to the second select only. I'm assuming you are using SQL Server here, but I guess it would be similar for Access.

There are two ways you can do this: put the (and name = 'xyz' and last_name = 'xyz') into each select statement as I'm sure you've already worked out, or get everything and then apply the where.

Note that the first option is probably more efficient, as you won't be a load of rows and then throwing most of them away.

To do the second way as you are asking, you will need to do all the unions as a sub-query then do a select and where name.. around it. Something like this should do it:
SQL Code:
SELECT CompanyID, CompanyName FROM
(
  SELECT supplier_id AS CompanyID, supplier_name AS CompanyName, [name], last_name
    FROM suppliers
    WHERE supplier_id > 2000
  UNION
  SELECT company_id AS CompanyID, company_name AS CompanyName, [name], last_name
    FROM companies
    WHERE company_id > 1000
) thesubquery
WHERE
  name = 'xyz' AND last_name = 'xyz'
ORDER BY CompanyName;
The main things to note here are:
1. We put the whole of the UNION query in brackets and give it a name of thesubquery. This lets us use it as if there were a normal table called thesubquery.
2. Along with the IDs and names the UNION query also needs to return the things we will filter on, name and last_name, otherwise these won't be available in the final where clause.
3. I gave the supplier_id/company_id and supplier_name/company_name columns aliases of CompanyID and CompanyName, to make it a little more obvious how to reference them outside the subquery.

Hope this makes sense.
Phil
 
Old June 17th, 2009, 06:22 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Everything Philip said is true, but why couldn't you just do this:

Code:

SELECT company_id AS id, company_name AS company, name, last_name
FROM companies
WHERE company_id > 1000 AND name = 'xyz' AND last_name = 'xyz'
UNION
SELECT supplier_id, supplier_name, name, last_name
FROM suppliers
WHERE supplier_id > 2000 AND name = 'xyz' AND last_name = 'xyz'
ORDER BY company
It would probably execute a bit faster. And would surely execute a *LOT* faster if either (or both) name or last_name is indexed in either table (or both tables).

Note that there is never any point in applying an ALIAS to the fields of any SELECT except the first in a UNION, as the field names are *ignored* except in the first SELECT.
 
Old June 17th, 2009, 07:52 PM
Authorized User
 
Join Date: Apr 2009
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I tried the same way you told but the query was so big (more than 50 lines) with 3 unions, so I thought If I can use some shorter way.

Thanks to both of you for your reply.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Date Parameter in a Union Query bright_mulenga Access VBA 8 January 25th, 2008 08:13 AM
Union query in Access?? lisabb Access 5 October 24th, 2007 10:04 PM
UNION QUERY Help Corey Access 1 October 27th, 2006 05:29 PM
UNION query. rupen Access 3 April 28th, 2006 02:49 AM
Error on Make-Table Query In Union Query rylemer Access 1 August 20th, 2003 07:42 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.