We help IT Professionals succeed at work.

combine sql statements

elliottbenzle
on
Medium Priority
231 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.
Comment
Watch Question

Author

Commented:
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
Senior Systems and Integration Developer
CERTIFIED EXPERT
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.