[thelist] [SQL] determining if one column contains another?
Sean G.
ethanol at mathlab.sunysb.edu
Mon Sep 29 10:28:54 CDT 2003
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.
More information about the thelist
mailing list