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.

 

[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?

 

[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.

 

[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