I have a query with a subquery. I would like the query to be able to use the
results from the subquery as part of what I am looking for.

e.g. at the moment in the query's WHERE clause I have:

WHERE THE_FIELD IN
(select THE_FIELD
from TABLE
where 'a condition is satisfied')

However, if one of the subquery results for the field in question was
'TEST', and in the main query I want to use this word and search for
'TEST%', is this possible.

Instead of the word IN what I need is SQL that lets me say LIKE WHAT IS IN
this subquery.

Is this possible? Is there another way?

Thanks.
Stephen