?
Solved

combine sql statements

Posted on 2006-11-08
2
Medium Priority
?
224 Views
Last Modified: 2010-04-25
I have two sql statements which I want to combine. One which searches the database for search terms and one which selects only entries where the parent ID column = "0" or the entered variable. I want the Sql to select only items which match both criteria (contain search terms AND parentid = "0"). The Sql is contained in a variable called"sqlString" which is defined early in the code. The parentid  = "0" part is contained in the "rsmessages.source =" part. I want to set the variable "sqlString" to contain both criteria and then enter the variable "sqlString" into the "rsmessages.Source =". Below are the two Sql statments which I want to combine. Can someone show me how to do this, I continue to get syntax errors when I try to.
 
this is the variable where I want the entire sql to be stored, right now it contains only the search terms ("wordsArray" = the search terms):

<%
Dim sqlString
sqlString = "SELECT * FROM forum2"
If UBound(wordsArray) > - 1 then
    sqlString = sqlString & " WHERE "
    For i = 0 to UBound(wordsArray)
       sqlString = sqlString & " subject LIKE '%" & wordsArray(i) & "%' OR message LIKE '%" & wordsArray(i) & "%' OR"
    Next
    sqlString = Left(sqlString, (Len(sqlString) - 3))
End if
%>

this is the sql which I would like to add into the varaible "sqlString" so that both criteria must be true in the database entries:

"SELECT *  FROM forum2  WHERE parentID = " + Replace(rsmessages__MMColParam, "'", "''") + "  ORDER BY ID DESC"

I hope this makes sense, if not please let me know.
0
Comment
Question by:elliottbenzle
2 Comments
 
LVL 4

Author Comment

by:elliottbenzle
ID: 17897457
I've almost got it, the only problem is that the final sql should group the two statements and I don't know how to do that. Here what the read out looks like:

SELECT * FROM forum2 WHERE parentID = 0 AND subject LIKE '%welcome%' OR message LIKE '%welcome%'

and I want it to work like this:

SELECT * FROM forum2 WHERE (parentID = 0) AND (subject LIKE '%welcome%' OR message LIKE '%welcome%')

with the two sql's bracketed, with an AND statement connecting them. Below is my code, can you show me how to change it so that the sql are both contained and then compared to each other to make the final Sql. Thanks.

Dim sqlString
sqlString = "SELECT * FROM forum2 WHERE parentID = " + Replace(rsmessages__MMColParam, "'", "''") + " AND"
If UBound(wordsArray) > - 1 then
    For i = 0 to UBound(wordsArray)
       sqlString = sqlString & " subject LIKE '%" & wordsArray(i) & "%' OR message LIKE '%" & wordsArray(i) & "%' OR"
    Next
    sqlString = Left(sqlString, (Len(sqlString) - 3))
End If
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 2000 total points
ID: 17899749
Try something like:

    Dim sqlString
    sqlString = "SELECT * FROM forum2 WHERE parentID = " + Replace(rsmessages__MMColParam, "'", "''") + " AND"
    If UBound(wordsArray) > - 1 then
        sqlString = sqlString & "("
        For i = 0 to UBound(wordsArray)
            sqlString = sqlString & " subject LIKE '%" & wordsArray(i) & "%' OR message LIKE '%" & wordsArray(i) & "%'"
            If i < UBound(wordsArray) Then sqlString = sqlString & " OR"
        Next
        sqlString = sqlString & ")"
    End If
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I still run into .cgi files every now and then. In some instances, I actually prefer the simplicity of a .cgi script to other options. Since I use DreamWeaver extensively, what I needed was a way to open .cgi scripts in Dreamweaver. And I wanted to …
This article is very specific and is only intended to help if you are installing Dreamweaver 8 in a Windows 7 environment with Office 2007 installed.   I'm not sure why Microsoft tends to release OS' that should not be released but they do.  Windows…
The video provides a quick and easy steps to migrate MBOX file to well known Outlook PST and Office 365. Besides this, it also supports and migrates more than 20 email clients of MBOX which include AppleMail, Opera, Thunderbird and SeaMonkey effortl…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question