Wildcard sub query

[Guest]

I have subquery and would like to use a wildcard for all records that begin
with data in subquery. How do I do this? The subquery except that I need it
to retrieve any records that begin with data in the tblColorCriteria table.
for instance, in the tblColorCriteria I have a data that says "Green",
"Blue", etc. The query needs to find all records in tblAllColors that are
like "Green*", "Blue*", etc.

Select colors from tblAllColors Where colors In (Select theseColors From
tblColorCriteria)

Thanks in advance

 

[John Vinson]

I have subquery and would like to use a wildcard for all records that begin
with data in subquery. How do I do this? The subquery except that I need it
to retrieve any records that begin with data in the tblColorCriteria table.
for instance, in the tblColorCriteria I have a data that says "Green",
"Blue", etc. The query needs to find all records in tblAllColors that are
like "Green*", "Blue*", etc.

Select colors from tblAllColors Where colors In (Select theseColors From
tblColorCriteria)

Thanks in advance

Use a non-equijoin:

SELECT colors
FROM tblAllColors INNER JOIN theseColors
ON tblAllColors.Colors LIKE theseColors.Colors & "*"

John W. Vinson[MVP]