Replacing SELECT with a value in an OUTER JOIN

[dumbledad]

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.

[Simon Hayes]

I'm not sure I totally understand, but perhaps something like this
might work:

INSERT INTO dbo.Words (Word, SomeNumber)
SELECT 'bounded', 1
where not exists (
select *
from dbo.GenericStopWords
where Word = 'bounded'
)

Or equivalently:

if not exists (
select *
from dbo.GenericStopWords
where Word = 'bounded'
)
INSERT INTO dbo.Words (Word, SomeNumber)
SELECT 'bounded', 1
You might want to consider putting this into a stored proc which takes
your word as a parameter - that's generally a better practice then
embedding all your SQL statements in the front end.

If this doesn't help, I suggest you post sample CREATE TABLE and INSERT
statements to provide a test case which others can copy and paste into
QA.

Simon

[dumbledad]

Thanks Simon. I'd been following a heuristic that "not exists" would
take longer than outer joins with a null test. I'll need to think that
through more. Your solution is exactly what I was after.

[John Bell]

Hi

If you are only splitting the string then you can use your original method
and create a subquery

INSERT INTO Words
SELECT g.word, 1
FROM ( SELECT 'I' as word
UNION ALL SELECT 'could'
UNION ALL SELECT 'be'
UNION ALL SELECT 'bounded'
UNION ALL SELECT 'in'
UNION ALL SELECT 'a'
UNION ALL SELECT 'nutshell'
UNION ALL SELECT 'and'
UNION ALL SELECT 'count'
UNION ALL SELECT 'myself'
UNION ALL SELECT 'a'
UNION ALL SELECT 'king'
UNION ALL SELECT 'of'
UNION ALL SELECT 'infinite'
UNION ALL SELECT 'space') g
LEFT OUTER JOIN dbo.GenericStopWords W ON g.word = w.word
WHERE g.word IS NULL

John

<du*******@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

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.