Stan (st*********@ho tmail.com) writes:
I'm looking for some hints as to how to use the results of a query in
another query. I assume I'm 'thinking' wrong in how to solve this, so
I'm hoping someone can clobber me and send me in the right direction.
My question is best illustrated by simplified example. I have one
table (table A) with a single column called "Word". This table
contains 100 records of single words. Another table (table B)
contains whole sentences in a single column. I want to eventually
count the occurances of all of the words in Table A, in each of the
sentences in Table B and store these results in another table. Here's
the 'thinking wrong' part. The only way I can see to do this is to
select the entire table A using a cursor, then select 'LIKE's of the
result against each individual row in table B. This seems horribly
inefficient, and I know there have to be one or two MUCH better ways
to do this, but I can't get me head wrapped around thinking in SQL at
this stage. Any info appreciated.
SQL is not the most optimal tool for this problem, and you may be better
off writing a Perl script that gets the data and performs the counting.
Provided that you Perl, that is. (Well, VBscript, C etc might also be
better candidates.)
And why is SQL not the right tool? Becase, as Celko, would put it: you
have your data model wrong. OK, may not really in this case, but your
sentence column has a repeating group of words, and repeating groups is
really a no-no.
Anyway, there is an article on my web site that deals in long detail
about unpacking a list of items into a table. Mainly the article discusses
this for a single list (typically an input parameter), but there is a
very brief section that discusses this for tables. The direct link to
that section is
http://www.sommarskog.se/arrays-in-s...#unpack-tblcol.
In your case, you would first have to strip the sentences of all punctuation
characters, so that you can use space as your delimiter.
--
Erland Sommarskog, SQL Server MVP,
so****@algonet. se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp