Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

combine sql statements

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
elliottbenzle
Asked:
elliottbenzle
1 Solution
 
elliottbenzleAuthor 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
0
 
Carl TawnSystems and Integration DeveloperCommented:
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now