SQL needed for tricky query

[mscertified]

I need the SQL to do the following, so far it escapes me.

I have 2 tables related in one to many relationship. The many sided table
has a text column.
I need to find all records in the one-sided table that contain a given
character string in any of the matching many-sided text columns.
I need an efficient way to do this. I'd prefer it to be a sub-select since
this search is going to be performed in conjunction with many other possible
searches.

E.g.
TableOne:
KeyID

TableMany:
KeyID
MyText

Find all KeyID in TableOne where MyText like '*abc*'

 

[Marshall Barton]

I need the SQL to do the following, so far it escapes me.

I have 2 tables related in one to many relationship. The many sided table
has a text column.
I need to find all records in the one-sided table that contain a given
character string in any of the matching many-sided text columns.
I need an efficient way to do this. I'd prefer it to be a sub-select since
this search is going to be performed in conjunction with many other possible
searches.

E.g.
TableOne:
KeyID

TableMany:
KeyID
MyText

Find all KeyID in TableOne where MyText like '*abc*'

SELECT TableOne.KeyID
FROM TableOne INNER JOIN TableMany
ON TableOne.KeyID = TableMany.KeyID
WHERE TableMany.MyText Like '*abc*'

It's not at all clear to me how you could introduce a
subquery into something this simple. Maybe this is what you
meant?

SELECT TableOne.KeyID
FROM TableOne
WHERE TableOne.KeyID IN(SELECT TableMany.KeyID
FROM TableMany
WHERE TableMany.MyText Like '*abc*')

but this one will probably be slower.

 

[John W. Vinson]

I need the SQL to do the following, so far it escapes me.

I have 2 tables related in one to many relationship. The many sided table
has a text column.
I need to find all records in the one-sided table that contain a given
character string in any of the matching many-sided text columns.
I need an efficient way to do this. I'd prefer it to be a sub-select since
this search is going to be performed in conjunction with many other possible
searches.

E.g.
TableOne:
KeyID

TableMany:
KeyID
MyText

Find all KeyID in TableOne where MyText like '*abc*'

SELECT TableOne.KeyID
WHERE KeyID IN
(SELECT KeyID FROM TableMany WHERE MyText LIKE "*abc*")

although an inner join will be MUCH more efficient:

SELECT TableOne.KeyID
FROM TableOne INNER JOIN TableMany
ON TableMany.KeyID = TableOne.KeyID
WHERE TableMany.MyText LIKE "*" & [Enter search term:] & "*"

John W. Vinson [MVP]