Using Like in Subquery

[Craig]

This is additional question from my post earlier:

I'm trying to do a subquery that uses "like" to find all companies in table2
that have a keword in their name that is found in table1.

The following returns the error "at most one record can be returned by this
subquery"

SELECT table2.*
FROM table2
WHERE (SELECT table1.keyword
FROM table1
WHERE table2![companyname] LIKE "*" & table1.keyword & "*");

Any help would be greatly appreciated. Thanks.

 

[Marshall Barton]

This is additional question from my post earlier:

I'm trying to do a subquery that uses "like" to find all companies in table2
that have a keword in their name that is found in table1.

The following returns the error "at most one record can be returned by this
subquery"

SELECT table2.*
FROM table2
WHERE (SELECT table1.keyword
FROM table1
WHERE table2![companyname] LIKE "*" & table1.keyword & "*");

Not speedy, but try:

SELECT table2.*
FROM table2 INNER JOIN table1
ON table2.companyname LIKE "*" & table1.keyword & "*"

You can not represent that kind of join in the query design
window so you will have to work in SQL view.

 

[david]

You lost the EXISTS part of the subquery example:

WHERE exists (SELECT table1.keyword

You can try all of the examples suggested in reply to your earlier post,
(and you can also use Instr inside your queries) but all of them will be
slow if your tables are large. Like "*key*" is always slow, and can't
be indexed, so it's a "cross query" whichever way you look at it.

"exists" returns at most one value (true or false). It converts your
subquery (which has the possiblilty of having multiple values) into
a single true/false value.

(david)

This is additional question from my post earlier:

I'm trying to do a subquery that uses "like" to find all companies in
table2
that have a keword in their name that is found in table1.

The following returns the error "at most one record can be returned by
this
subquery"

SELECT table2.*
FROM table2
WHERE (SELECT table1.keyword
FROM table1
WHERE table2![companyname] LIKE "*" & table1.keyword & "*");

Any help would be greatly appreciated. Thanks.

 

[Craig]

Thanks...you're right, not too speedy, but works... ;o)