Solved

combine sql statements

Posted on 2006-11-08
2
218 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

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 …
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…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

627 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