Union Query Help

Nishapd · June 17th, 2009, 12:20 PM

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 ?

philip_cole · June 17th, 2009, 03:23 PM

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 Pedant · June 17th, 2009, 06:22 PM

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.

Nishapd · June 17th, 2009, 07:52 PM

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.