[thelist] [SQL] determining if one column contains another?

[Sean G.]

Howdy,

I'm trying to find records in a table for which one column contains values
from another table.  I'm working with MS-SQL 2000.  (I also have Access
2000, but I figure anything I can do with Access I can do with SQL, but not
necessarily vice versa.)

I have a table of chemical results and a table of canned comments from
reviewers, and I'm trying to make a report which counts the number of times
each comment occurs in a set of results.

For example, I have something like,

libComments
ID,    Comment
-----------
1      Too hot.
2      Too cold.
3      Too hard.
4      Too soft.

tblResults
ID,      Result,      RevComment
-------------------------------
1        99           Too hot.
2        0            Too cold./Too hard.
3        37           Just right.
4        15           Too cold./Too soft.

RevComment can contain 1 or more canned comments from libComments, plus any
ad hoc comments.

So what I'd like is a query to get something like,

MagicQuery
Result,      RevComment,           Comment
-----------------------------------------------
99           Too hot.              Too hot.
0            Too cold./Too hard.   Too cold.
0            Too cold./Too hard.   Too hard.
37           Just right.
15           Too cold./Too soft.   Too cold.
15           Too cold./Too soft.   Too soft.

>From which I can count up the number of results for each Comment.  My issue
is it seems for T-SQL LIKE and CONTAINS require literal values and do not
work with table columns.  So I can't do something like,

SELECT   Result, RevComment, Comment
FROM     tblResults LEFT JOIN
         libComments ON tblResults.RevComment LIKE %libComments.Comment%

I have about 40 fixed records in libComments, so I can hard code the canned
Comments into a query if I must, but I'd rather come up with something a
little more elegant.  I cannot make significant changes to existing data,
such as replacing the RevComment text with the libComments ID, but I can
make additions to the database, such as a join table of tblResults and
libComments IDs.

Any thoughts or suggestions are appreciated.

TIA,


Sean G.