Posted by dumbledad@gmail.com on 09/06/05 14:20

Hi All,

I'm confused by how to replace a SELECT statement in a SQL statement
with a specific value. The table I'm working on is a list of words (a
column called "word") with an index int pointing to the sentence they
come from (a column called "regret"). I also have a table of stop words
(called "GenericStopWords") that contains the words I do not want to
consider. That table has a single column called "word".

I started off using a SQL function (called "GETALLWORDS") which
returned the words of a string. This is how I used it:

INSERT INTO Words
SELECT wrds.WORD, 1 FROM
GETALLWORDS('I could be bounded in a nutshell and count myself a king
of infinite space', default) AS wrds
LEFT OUTER JOIN
GenericStopWords
ON wrds.WORD = GenericStopWords.word
WHERE GenericStopWords.word IS NULL

This statement inserts every word in the sentence that is not a stop
word. So, for example, ('bounded', 1) is added but ('could', 1) is not
added.

I've now decided to move the function that breaks a string into words
out of the SQL layer of my application and into the JavaScript which
runs under ASP on the web server. So now I want to call something
analogous to the above SQL statement, but word by word. What I'd like
is something like:

INSERT INTO Words
SELECT 'bounded', 1 FROM ???
LEFT OUTER JOIN
GenericStopWords
ON 'bounded' = GenericStopWords.word
WHERE GenericStopWords.word IS NULL

Does that make sense? I want a SQL statement that will insert the tuple
('bounded', 1) into the table Words if (and only if) 'bounded' does not
appear in the table GenericStopWords. It's easy to say procedurally, I
cannot see how to write it in a relational style in SQL.

Thanks in advance for any help you can give.

Cheers,

Tim.

• Next in forum: Re: Massive delete in DB
• Prev in forum: Massive delete in DB
• Thread view: Replacing SELECT with a value in an OUTER JOIN

[Reply to this message]