473,287 Members | 1,659 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Replacing SELECT with a value in an OUTER JOIN

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.

Sep 6 '05 #1
3 5685
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

Sep 6 '05 #2
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.

Sep 6 '05 #3
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.

Sep 6 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: jagg | last post by:
Hi, i save junior football results in a mysql table (FIELDS id jugend autor sptag ergebnis spdatum zeit ) With the following lines I generate a site whichs gives me ALL results Code:...
1
by: Gerald Maher | last post by:
Hi I need to select 2 tables . The field 'p_description' is not always aviable but i still need to print it to the screen SELECT * FROM task,p_description WHERE ComponentIDLink = 34 OR...
3
by: Ian T | last post by:
Hi, I've got what I think (probably incorrectly) should be a simple SELECT : Two colums with data like col1 col2 1 50 1 51 2 50
5
by: David Logan | last post by:
Hello, I am trying to construct a query across 5 tables but primarily 3 tables. Plan, Provider, ProviderLocation are the three primary tables the other tables are lookup tables for values the...
1
by: brett | last post by:
Here is my SQL string: "SELECT to_ordnum, to_orddate," _ & "(SELECT SUM((DDPROD.pr_stanmat * DDPROD.pr_prfact) * (DOBOM2.b2_quant * DDORD.or_quant)) FROM DDPROD INNER JOIN DOBOM2 ON...
5
by: Reestit Mutton | last post by:
Hi, I'm currently learning the ropes with CSS, PHP, MYSQL, Javascript etc... by redesigning my website as a database driven site. Okay, so I'm skilled at perl, data manipulation and data...
4
by: Anna Smith | last post by:
I have an extremely long and complex select statement as shown below and I need to split it onto two lines but not quite sure how to do it because I've never needed to before. I need to split it...
6
by: Bob Stearns | last post by:
I am getting duplicate rows back from a select distinct statement of the form: SELECT DISTINCT 'jhough', '000111', t0.bhid FROM (SELECT lots of good stuff) t0 LEFT OUTER JOIN another_table ...
4
by: Jean-Claude | last post by:
Hi, which is the faster query ? (of course, in my case the real queries are more complex) 1/ select * from file1 a join file2 b on b.key=a.key where b.data=123 and b.name='TEST'
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.