Boolean Searches

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-

stereo and speakers not radio

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.

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.

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!

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.

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:

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:

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.

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!

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).

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.

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful.
Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…

This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.