help with subqueries and wildcards

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Eric - 19 Jul 2006 20:49 GMT

Hi all,
I am having trouble with using wildcards within a subquery. More
specifically, I'm trying to pull information on certain model machines
from my main table, while excluding models that I have entered into a
separate table.

so my query looks like this thus far:

SELECT all_machines.*
FROM all_machines WHERE (all_machines.Model <> ANY (SELECT
excluded_models.excluded FROM excluded_models))

the problem that I am having is that the query correctly excludes all
the models where I have explicitly written the complete model number
(ex. ICOPY500) but it does not exclude the models where I have written
them using wildcards (ex. ECOPY*). I believe that the query ends up
looking for the exact text ICOPY* rather than recognizing the * as a
wildcard, but I don't know how to fix it. I have tried to insert a LIKE
function, but i keep getting errors. Any ideas?

Thanks in advance

Sincerely,
Eric

Reply to this Message

Michel Walsh - 20 Jul 2006 00:32 GMT

Hi,

Indeed, you will need the operator like, but like does NOT support universal
quantifier (ALL, SOME, ANY):

SELECT*
FROM machines
WHERE NOT Model LIKE ANY (SELECT excluded FROM excluded_models)

so, the previous statement won't work, but you can use EXISTS:

SELECT *
FROM machines
WHERE NOT EXISTS( SELECT * FROM excluded_models WHERE machine LIKE excluded)

Hoping it may help,
Vanderghast, Access MVP

> Hi all,
> I am having trouble with using wildcards within a subquery. More
[quoted text clipped - 20 lines]
> Sincerely,
> Eric

Reply to this Message