Tuesday, March 27, 2012

Combining Queries/ Results

I have created a search interface for a large table and I allow users to search on keywords. The users can enter multiple keywords and I build a SQL based on their input to search a full-text indexed table.
However the users want to be able to search like an old system they had, where they enter single words and then combine their searches to drill-down into the results.
What would be the best method to combine searches?
At the moment I can create a merged query from 2 queries if they have searched using single words, but I know down the line it will get far more complicated if they keep combining and merging even with multiple word entries.
Each time they search I store the 'where' section of each query, then if they choose to combine I have a function to build a new query through arrays (to eliminate duplicates and sort etc)
Is there a better way in SQL to combine queries as sometimes the logic of the combined query means no results are returned (because of OR/ AND conditions in the wrong places etc)
e.g.
1. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"'))
2. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"level"') OR CONTAINS(subject,'"level"'))

Combined using my function creates:
Select count(ID) as myCount FROM myTable where (contains(title,'"level"') AND contains(title,'"run"')) OR (contains(subject,'"level"') AND contains(subject,'"run"'))

When I combine I'm drilling down, so if the first query returns a count of 400 (where thetitleORsubjectcontains 'run') and then the second query returns 600 records (where thetitleORsubjectcontains 'level') I need to combine so that I'm looking for records where thetitlecontains both keywords 'run' AND 'level' OR else thesubjectcontains both 'run' AND 'level' and I end up with say 50 records where the title has both keywords OR the subject holds both words.
I think the main trouble lies if they try combine a previously combines search with a new search. here my logic gets totally thrown and I'm not sure how to handle soemthing like this. Has anyone got any ideas or experience with this kind of functionality? In SQL or even in vb.net is there a method to combine searches easily?

You don't need to build it like you are. Just keep adding ANDswith the appropriate conditions. SQL will figure it out.
Example:
S1: WHERE (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"')
S2: WHERE (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"')
AND (CONTAINS(title,'"level"') OR CONTAINS(subject,'"level"'))
S3: WHERE (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"')
AND (CONTAINS(title,'"level"') OR CONTAINS(subject,'"level"'))
AND (CONTAINS(title,'"blah"') OR CONTAINS(subject,'"blah"'))
...

No comments:

Post a Comment