mutually exclusive UNION query

[casey]

Hello everyone,

Using a UNION statement, can I retrieve records that are mutually exclusive in each SQL statement?

Something like this, but where all records are returned, but the TOP 5...

SELECT Products FROM tblProducts '<<returns all products
UNION
SELECT TOP 5 Products FROM tblProducts;'<<eliminate these TOP 5

Any ideas how to go about this?

Thanks.

 

[Pat Hartman]

A union won't help you. You need a join or a sub-select. Since sub-selects are less efficient, I'll give an example of a join.

query1:
Select Top 5 SELECT TOP 5 Products FROM tblProducts
Order By Something;

query2:
Select Products
From tblProducts as t Left Join query1 as q ON t.Products = q.Products
Where q.Products Is Null;

When you run query2, you should get all products EXCEPT the top 5.

 

[casey]

Thanks for the help, Pat. I'll give that a shot.

 

[casey]

This is the query that I'm using...

"SELECT DISTINCT TOP 9 t.Category FROM tblMenu AS t LEFT JOIN (SELECT DISTINCT TOP 9 Category FROM tblMenu ORDER BY Category) AS q ON t.Category = q.Category WHERE q.Category Is Null;"

It's the same as the previous query w/ different fields, table names and the fact that it contains a subquery. It works exactly as I'd hoped. (Thank you).

One question)...
Is it O.K. to insert a query inside another query this way or should I be using a separate query and reference it, the way you did? It works now, but can it cause problems?

 

[Pat Hartman]

I use separate queries because they are easier for Jet to optimize. Sub-queries can be VERY slow so I don't even bother with trying them.

 

[casey]

Pat, despite your warnings about subqueries, I had to use them for this application. As it stands, it's pretty quick and I think it will be O.K. because the database is relatively small (about 40 to 50 records).
How many records would it take to slow the query down to where it would be noticable to the user (10,000 or 1,000,000)? I realize the answer is probably machine dependent, but could you elaborate on the problems that may ensue if I continue in this direction?

Thanks.

 

[Pat Hartman]

I can't give you a point at which you will notice a difference between nested queries and a sub-select.

I had to use them for this application

I don't know why you think you needed a sub-select. If you are using the query as a recordsource for a form or report you would just bind the form/report to query2 from my example.

 

[casey]

I guess I could figure it out using separate queries and instead of citing reasons for doing it the way I have, I'll try to get it working using separate queries.

Thanks once again