Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Ferox
Starting Member
18 Posts |
 Posted - 2005-05-04 : 06:23:13
|
| 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"'))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 is there a method to combine searches easily? |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2005-05-04 : 06:32:07
|
I'm not sure why you are changing your AND and OR operators. To combine the two above in 1 and 2 you would needSelect count(ID) as myCount FROM myTable where (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"')) OR where (CONTAINS(title,'"level"') OR CONTAINS(subject,'"level"')) -------Moo. :) |
 |
|
|
Ferox
Starting Member
18 Posts |
Posted - 2005-05-04 : 06:49:25
|
| Thanks for the reply. When I combine I'm drilling down, so if the first query returns a count of 400 (where the title OR subject contains 'run') and then the second query returns 600 records (where the title OR subject contains 'level')I need to combine so that I'm looking for records where the title contains both keywords 'run' AND 'level' OR else the subject contains both 'run' AND 'level' and I end up with say 50 records where the title has both keywords OR the subject holds both words. If I use your query I get 1000 results returned. Maybe I didn't explain myself so well. :) |
|
|
|
Ferox
Starting Member
18 Posts |
Posted - 2005-05-05 : 05:15:21
|
| I was able to store each search, then if a user decides to combine I just add the two searches together with an 'AND'. Your right mr_mist I didn't need to change my operators at all! I was thinking of doing this a far more complicated way (storing each 'contains' in an array, then purge the duplicates and build a new query from the remainder! OTT!!)Now I just use: where (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"'))AND (CONTAINS(title,'"level"') OR CONTAINS(subject,'"level"'))Thanks for you input again mr_mist! |
|
|
|
|
|
|
|
|
Combine Queries