Using results of query in a query

[Stan]

Hello all,
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.

Thanks,
Stan

[Simon Hayes]

st*********@hot mail.com (Stan) wrote in message news:<9a******* *************** ****@posting.go ogle.com>...

Hello all,
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.

Thanks,
Stan

Something like this may get you started:

select
w.word,
p.phrase,
(len(p.phrase) - len(replace(p.p hrase, w.word, ''))) / len(w.word) as
'Count'
from dbo.Words w
join dbo.Phrases p
on p.phrase like '%' + w.word + '%'

This isn't very accurate because of short words which occur inside
larger ones, but it may be good enough, depending on your needs. You
can also have issues with punctuation, word boundaries etc. You might
need to consider doing this outside the database, with a language
which has better text processing capabilities.

Simon

[Erland Sommarskog]

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