Solved

combine sql statements

Posted on 2006-11-08
2
213 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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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 …
Adobe Dreamweaver CS5 is a WYSIWYG web page editor that has advanced HTML, CSS, and Javascript rendering functionality and is probably the most well-known HTML editor available. Much of Dreamweaver's appeal centers around the Design View interfac…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now