# Boolean Searches

Hi,

I am trying to make it so someone can search my database for something like:

5 and 30 not 31

-or-

john and jane

-or-

candle or wax

-or-

I'm sure you get the point.

The point is I don't know what they are going to search for, so my frontend program (written in VB) can't just search based on knowing what fields.  I can't do a freetext file or whatever it is - so is it possible to do boolean searches.  Or take a string (from the combo box where they enter the search) and build it to search the DB based on that criteria?  IF I need to be a bit more clear let me know.
###### Who is Participating?

Senior DBACommented:
Then El Jefe will need to provide you with the parsing logic!  ;-)

Well, let's make some assumptions, and make the code more complex:

Assumption:  You need parentheses for grouping
Reason:  green and blue or red and yellow not puce is different from (green and blue) or (red and yellow) not puce

Assumption:  You know whether or not a field is numeric {Duh}

Assumption:  Numeric values will only compare to numeric fields.

Consider this case:

blue and xxl and 1999 not 3.5

If all fields are compared with all values, your WHERE clause might be:

(Data Fields:  Name, Desc, Year, Size, Price, Color, Country)

SELECT * From MyTable Where ((Name Like '%blue%' and Name like '%xxl%')
OR (Desc Like '%blue%' and Desc like '%xxl%')
OR (Size Like '%blue%' and Size like '%xxl%')
OR (Color Like '%blue%' and Color like '%xxl%')
OR (Country Like '%blue%' and Country like '%xxl%'))
AND ((Year = 1999 AND NOT Year = 3.5)
OR (Price = 1999 AND NOT Price = 3.5)

Clearly, this won't work.  Thus, you would have to add logic to your code to determine that a given field should be compared to a given value.

An alternate (and even slower) method that should mostly return correct results would be:

Select * From (Select * From (Select * From (SELECT * From MyTable Where Name Like '%blue%' OR Desc Like '%blue%' Or Size Like '%blue%' Or Color Like '%blue%') AS A
WHERE A.Desc like '%xxl%' OR A.Size like '%xxl%' OR A.Color like '%xxl%' OR A.Country like '%xxl%') As B
WHERE B.Year = 1999 OR B.Price = 1999) As C
WHERE C.Year = 3.5 OR C.Price = 3.5

Adding an OR, or parentheses, into the computation would entail using a UNION query to generate the resultset:

(blue or green) and xxl and 1999 not 3.5

Select * From (Select * From (Select * From (SELECT * From MyTable Where Name Like '%blue%' OR Desc Like '%blue%' Or Size Like '%blue%' Or Color Like '%blue%'
UNION
SELECT * From MyTable Where Name Like '%green%' OR Desc Like '%green%' Or Size Like '%green%' Or Color Like '%green%') AS A
WHERE A.Desc like '%xxl%' OR A.Size like '%xxl%' OR A.Color like '%xxl%' OR A.Country like '%xxl%') As B
WHERE B.Year = 1999 OR B.Price = 1999) As C
WHERE C.Year = 3.5 OR C.Price = 3.5

This would be functional, but would be hell on your DB Server, and would *NOT* scale to more than a few simultaneous queries at all well, due to the multiple temp tables, table scanning, etc.

However, if conditions were entered by field:

NAME:
DESC:
COLOR:  green or blue not sky
SIZE:   xxl
YEAR:   1999     0 Equal to    0 No later than   0 No earlier than
PRICE:  3.5      0 No more than    0 Exactly

(assuming Equal To and No More Than are chosen as options)

Then your query would come out as:

SELECT * From MyTable WHERE ((Color Like '%green%' Or Color Like Blue) And Color Not Like '%sky%')
AND (size = '%xxl%')
AND (Year = 1999)
AND (Price <= 3.5)

Which would execute dozens, if not hundreds, of times as fast.

Argue the case with examples like this.  The other method will be slow, will *feel* slow to users, and will not be as accurate.

Remind El Jefe that the Web searches doing things like this (I assume he's thinking something like a Yahoo or Google search) use a Full-Text index on all of the data.  That's not what he wants here (and you would have to copy all of the data in the record into one text field for it to work anyway).

Good Luck.
0

Senior DBACommented:
Looking at your example selections, you'll need:

(1) A parsing module, to separate the conditions into their logical components

(2) A query builder.

Example:

The WHERE for this would be:

WHERE MyField LIKE '%stereo%' AND MyField Like '%speakers%' AND MyField NOT Like '%radio%'

So, your parsing code needs to be something like:

Function ParseConditions(ByVal Cond as String, ByVal CompareFieldName As String) As String
Dim iPos as Integer, sWork as String
Dim sItems() As String
Dim bLastLogical As Integer

Const cAND = 1
Const cOR = 2
Const cNOT = 3

If Trim(Cond) = "" Then
ParseConditions = ""
Exit Function
End If

sItems = Split(Cond," ")
iPos = 0
bLastLogical = True
Do While iPos <= UBound(sItems)
Select Case UCase(sItems(iPos))
Case "AND"
If bLastLogical = 0 AND iPos <> UBound(sItems) Then
sWork = sWork & " AND " & CompareFieldName & " LIKE '%"
bLastLogical = cAND
End If
Case "OR"
If bLastLogical = 0 AND iPos <> UBound(sItems) Then
sWork = sWork & " OR " & CompareFieldName & " LIKE '%"
bLastLogical = cOR
End If
Case "NOT"
If bLastLogical = 0 AND iPos <> UBound(sItems) Then
sWork = sWork & " AND " & CompareFieldName & " NOT LIKE '%"
bLastLogical = cNOT
End If
Case ""
' Ignore - extra space
Case Else
If sWork = "" Then sWork = "WHERE " & CompareFieldName & " Like '%"
sWork = sWork & sItems(iPos) & "%'"
bLastLogical = 0
End Select
iPos = iPos + 1
Loop
If bLastLogical <> 0 Then
SELECT CASE bLastLogical
CASE True
sWork = ""
CASE cAND
sWork = Right(sWork, Len(sWork) - (Len(CompareFieldName) + 13)
CASE cOR
sWork = Right(sWork, Len(sWork) - (Len(CompareFieldName) + 12)
CASE cNOT
sWork = Right(sWork, Len(sWork) - (Len(CompareFieldName) + 17)
End Select
End If
ParseConditions = sWork
End Function
0

Author Commented:
If I do it that way won't the SQL string end up too long?  or is that not an issue?

The other thing...lets say they do

so I could end up needing to call three separate fields - but I don't know what they are going to search for...this is going to be a major pain in the arse!

so in the above example...

price = 300

year != 2000

Your example won't find the fields that match that right?  Only if they are all in the same field...?
0

Author Commented:
by the way that's why I made this a 300 point question...I knew it was going to a difficult answer!  I have spent the last 2 weeks trying to figure this out (SQL newbie here)
0

Commented:
Interesting problem.  Essentially, it sounds like you need to reporduce the access query builder for your users.  Can you tell me a little more about your table - field count etc...?  Do you need to to simulate joins or unions?  Please say no.  It sounds like a neat challange.  One suggestion: for OR conditions on the same field, I normally like to generate a dynamic IN list.
0

Author Commented:
tmpVar = Split(tmpSQL, Chr(32))

For L = LBound(tmpVar) To UBound(tmpVar)

If LCase(tmpVar(L)) = "and" Then
tmpBuild = tmpBuild & " AND"
ElseIf LCase(tmpVar(L)) = "or" Then
tmpBuild = tmpBuild & " OR"
ElseIf LCase(tmpVar(L)) = "not" Then
stbs = True
stbs2 = True
Else
If stbs = False Then
tmpBuild = tmpBuild & " [Field*] LIKE '%" & tmpVar(L) & "%'"
Else
stbs = False
tmpBuild = tmpBuild & " AND [Field*] NOT LIKE '%" & tmpVar(L) & "%'"
End If
End If

Next

' then I replace the [Field*] with my fields names

I think that's pretty much the same as what you gave me...the problem is I can't let the user do a search for 'radio and 2001', or 'radio and sony' as examples.  I have another window that lets people do stuff like that - where I prefill data based on the DB...but a search window is critical!
0

Author Commented:
Falkor, I have 20 fields, one table...however it can be done is fine by me.  As far as I know I don't need joins or unions...  A dynamic IN list sounds like a good idea that I hadn't thought of!
0

Commented:
Interesting problem.  Essentially, it sounds like you need to reporduce the access query builder for your users.  Can you tell me a little more about your table - field count etc...?  Do you need to to simulate joins or unions?  Please say no.  It sounds like a neat challange.  One suggestion: for OR conditions on the same field, I normally like to generate a dynamic IN list.
0

Senior DBACommented:
Hmmm... you may need to have multiple condition boxes on your front end, for the fields needed for the compare.  Consider something like:

Item Desc: ________________
Price: __________________
Year: ___________________
etcetera...

Think of how a price matching or item finding web site works (e.g. Travelocity).  You need to specify multiple different fields when defining your query on these sites, and your front end will either need to match that sort of logic, or require entering the field name with each criteria entered (not a good idea).

If you have separate input fields, you can also have different types of parsing.  For example, with your Price field, you could have the user entering a maximum dollars amount, and build your query directly like:

"PRICE <= " & txtPrice.Text

Now, assuming you have multiple text fields (which is what the above parsing logic is set to handle), you would call the function multiple times:

Sub cmdGo_Click ()
Dim sWork as String, sFinal as String
Dim sQuery as String

If txtDescConditions <> "" Then
sWork = ParseConditions(txtDescConditions, "Description")
sFinal = sWork
End If

If txtReviewConditions <> "" Then
sWork = ParseConditions(txtReviewConditions, "Review")
If sFinal = "" Then
sFinal = sWork
Else
sFinal = sFinal & " AND " & Mid(sWork, 7)
End If
End If

If txtStoreLocConditions <> "" Then
sWork = ParseConditions(txtStoreLocConditions, "Store_Location")
If sFinal = "" Then
sFinal = sWork
Else
sFinal = sFinal & " AND " & Mid(sWork, 7)
End If
End If

....

If Val(txtPriceCond) > 0 Then
If sFinal = "" Then sFinal = "WHERE " Else sFinal = " AND "
sFinal = sFinal & "Price <= " & Format(Val(txtPriceCond),"##0.00")
End If

....

sQuery = "SELECT * From MyTable"
If sFinal <> "" Then sQuery = sQuery & " " & sFinal

0

Senior DBACommented:
Hmmm... you may need to have multiple condition boxes on your front end, for the fields needed for the compare.  Consider something like:

Item Desc: ________________
Price: __________________
Year: ___________________
etcetera...

Think of how a price matching or item finding web site works (e.g. Travelocity).  You need to specify multiple different fields when defining your query on these sites, and your front end will either need to match that sort of logic, or require entering the field name with each criteria entered (not a good idea).

If you have separate input fields, you can also have different types of parsing.  For example, with your Price field, you could have the user entering a maximum dollars amount, and build your query directly like:

"PRICE <= " & txtPrice.Text

Now, assuming you have multiple text fields (which is what the above parsing logic is set to handle), you would call the function multiple times:

Sub cmdGo_Click ()
Dim sWork as String, sFinal as String
Dim sQuery as String

If txtDescConditions <> "" Then
sWork = ParseConditions(txtDescConditions, "Description")
sFinal = sWork
End If

If txtReviewConditions <> "" Then
sWork = ParseConditions(txtReviewConditions, "Review")
If sFinal = "" Then
sFinal = sWork
Else
sFinal = sFinal & " AND " & Mid(sWork, 7)
End If
End If

If txtStoreLocConditions <> "" Then
sWork = ParseConditions(txtStoreLocConditions, "Store_Location")
If sFinal = "" Then
sFinal = sWork
Else
sFinal = sFinal & " AND " & Mid(sWork, 7)
End If
End If

....

If Val(txtPriceCond) > 0 Then
If sFinal = "" Then sFinal = "WHERE " Else sFinal = " AND "
sFinal = sFinal & "Price <= " & Format(Val(txtPriceCond),"##0.00")
End If

....

sQuery = "SELECT * From MyTable"
If sFinal <> "" Then sQuery = sQuery & " " & sFinal

0

Senior DBACommented:
One more note:  I should modify the CASE in my original entry to handle the correct entry:

--------------

Select Case UCase(sItems(iPos))
Case "AND"
If bLastLogical = 0 AND iPos <> UBound(sItems) Then
sWork = sWork & " AND " & CompareFieldName & " LIKE '%"
bLastLogical = cAND
End If
Case "OR"
If bLastLogical = 0 AND iPos <> UBound(sItems) Then
sWork = sWork & " OR " & CompareFieldName & " LIKE '%"
bLastLogical = cOR
End If
Case "NOT"
' Change - Handle initial NOT in string
If bLastLogical = 0 AND iPos <> UBound(sItems) Then
sWork = sWork & " AND " & CompareFieldName & " NOT LIKE '%"
bLastLogical = cNOT
ElseIf bLastLogical < 0 AND iPos <> UBound(sItems) Then
If sWork = "" Then
sWork = "WHERE " & CompareFieldName & " NOT LIKE '%"
Else
sWork = sWork & " AND " & CompareFieldName & " NOT LIKE '%"
End If
End If
Case ""
' Ignore - extra space
Case Else
If sWork = "" Then sWork = "WHERE " & CompareFieldName & " Like '%"
sWork = sWork & sItems(iPos) & "%'"
bLastLogical = 0
End Select
iPos = iPos + 1
0

Commented:
On your interface, do you have them typing in their search criteria, or selicting from multi select list boxes, already populated with distinct lists?
0

Author Commented:
I have one window where there are populated list and combo boxes...but this isn't involving that...it's a combo box (just because I have a search history) where they would type something like:

0

Author Commented:
bhess1...that's a lot of code (which maybe fine) however I am a little confused on one point...are you building the SQl string so it tries to satisfy each thing?

Something like this? (I'm going to use two fields for simplicity)

Search term: Sony and radio not 2000

select * from mytable where myfield1 like %parm(1)% or myfield2 like %parm(1) and myfield1 like %parm(2)% or myfrield2 like %parm(2)% and myfield1 not like %parm(3)% and myfield2 not like %parm(3)%

Am I corrrect?
0

Author Commented:
select * from mytable where myfield1 like %Sony% or myfield2 like %Sony% and (myfield1 like %radio% or myfrield2 like %radio%) and (myfield1 not like %2000% and myfield2 not like %2000%)
0

Author Commented:
something like that?  Sorry this is a bit over my head...I have a bad grasp on anything above simple select/update/insert/delete statements.
0

Senior DBACommented:
No, not at all.  Basically, I am recommending that you input conditions by field.  Certainly, it is possible to check all possible fields for all possible conditions, but this is *NOT* an efficient way to query a database.  Your overhead and query execution time would be horrendous.

Instead, you need to specify your conditions separately for each field that can have conditions, and parse out the conditions into one large WHERE field.

In my last post, at the top, there was a quick example:

Item Desc: ________________
Price: __________________
Year: ___________________
etcetera...

In this example, you might enter:

Item Name: coat
Item Desc: green or blue not pink
Price: 199.95
Year: ___________________

Processing this, your code might look like my example above, with different field names:

Sub cmdGo_Click ()
Dim sWork as String, sFinal as String
Dim sQuery as String

If txtNameConditions <> "" Then
sWork = ParseConditions(txtNameConditions, "Item_Name")
sFinal = sWork
End If

If txtDescriptionConditions <> "" Then
sWork = ParseConditions(txtDescriptionConditions, "Item_Description")
If sFinal = "" Then
sFinal = sWork
Else
sFinal = sFinal & " AND (" & Mid(sWork, 7) & ")"
End If
End If

If Val(txtPriceCond) > 0 Then
If sFinal = "" Then sFinal = "WHERE " Else sFinal = " AND "
sFinal = sFinal & "Price <= " & Format(Val(txtPriceCond),"##0.00")
End If

If Val(txtYearCond) > 0 Then
If sFinal = "" Then sFinal = "WHERE " Else sFinal = " AND "
sFinal = sFinal & "Year = " & Format(Val(txtYearCond), "0000")
End If

sQuery = "SELECT * From MyTable"
If sFinal <> "" Then sQuery = sQuery & " " & sFinal

End Sub

Your final query in this case would look like:

SELECT * From MyTable WHERE Item_Name LIKE '%coat%' AND (Item_Desc Like '%green%' OR Item_Desc Like '%blue' AND Item_Desc NOT Like '%pink%') AND Price <= 199.95
0

Commented:
This is just a suggestion:

How about concatenating all the columns and use the "like" and "not like" operators in the SQL statement?. If you have number columns, you can convert them to CHAR before concatenating it.

NOTE: The biggest problem with this approach would be that the query would possibly perform a table scan. i.e. it would have slow response time.
0

Author Commented:
mathavra...can you give me an example of what you mean?

Bhess1, I agree that's the best way to do it...however, I need to have a search box that they can enter boolean search phrases in.  El Jefe demands it! haha.  I already have another window that lets the user do that, but El jefe thinks that may turn some people off...down with El jefe!
0

Commented:
Here is sample set of code & statements:

1> use tempdb
2> go
1> create table table1
2> (
3> col1 char(10),
4> col2 money,
5> col3 char(5)
6> )
7> go
1> insert into table1 values ("John", 1000, "XXXX")
2> insert into table1 values ("Simba",2000, "Ron")
3> insert into table1 values ("John",2000, "Jane")
4> insert into table1 values ("John",1000,"Jane")
5> go
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
1>

1> select
2> col1 + convert(char(15), col2) + col3
3> from table1
4> where
5> col1 + convert(char(15), col2) + col3 like "%John%"  and
6> col1 + convert(char(15), col2) + col3 like "%Jane%"  and
7> col1 + convert(char(15), col2) + col3 not like "%2000%"
8> go

------------------------------
John              1000.00Jane

(1 row affected)
0

Author Commented:
bhess, I agree with your logic :)  I think I am going to copy your post and send email a dirty email saying "REVOLUTION!"  With a DB that is about 1-2 gigs this thing is gonna take 3 days to complete a query...he's gonna have to accept that fact...that'll teach him to not let me get my full-text indexing on

*DOWN WITH EL JEFFE!*

I appreciate everyone elses input, but this is gonna be too damn taxing on the system resources and is going to bog down with multiple people running queries.  So I must go with El Jeffe's I mean Bhess's comments.

Thanks :)
0