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

Column space

Is there a command so that I can enter in my query to make a 1 space before
my results. The reason is that I'm currenlty running a report that gives me
patients demographics in a .CSV file and I used DTS to automate the process
the last information on each patient is SS# this file will then need to be
imported to to another vendor application. The problem is that the vendor
found a bug on their application that cuts the first digit on the SSN and in
order for them to fix their app. it will take months and I can't wait this
long. So what i'm trying to do is move the results on the last field (SSN#)
one space to the right so that the results on the .CSV file will be
,*111-11-1111
* blank space
Is this possible without manually editing the .CSV file?
Thanks,
"Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
news:29A4E586-F079-4471-8AB4-DF58E0D5E4E9@.microsoft.com...
> Is there a command so that I can enter in my query to make a 1 space
> before
> my results. The reason is that I'm currenlty running a report that gives
> me
> patients demographics in a .CSV file and I used DTS to automate the
> process
> the last information on each patient is SS# this file will then need to be
> imported to to another vendor application. The problem is that the vendor
> found a bug on their application that cuts the first digit on the SSN and
> in
> order for them to fix their app. it will take months and I can't wait this
> long. So what i'm trying to do is move the results on the last field
> (SSN#)
> one space to the right so that the results on the .CSV file will be
> ,*111-11-1111
> * blank space
> Is this possible without manually editing the .CSV file?
> Thanks,
There are a couple of ways of handling this.
1. Does it have to be a space? I would suggest some other identifying
mark like an asteriks (*) or a tilde(~). That way any TRIM type functions
won't erase that space.
2. In the query itself, you can do something like this:
SELECT col1, col2, '*' + SSNColumn
FROM tablename
3. If that is undesirable, then you can always create a view with the above
SELECT statement and then run DTS against the view rather than the base
table.
Rick Sawtell
MCT, MCSD, MCDBA
|||So if I want to try this without the asteriks (*) or a tilde(~). I would
type my query as: SELECT col1, col2, + SSNColumn
FROM tablename
Or: SELECT col1, col2, '' + SSNColumn
FROM tablename
"Rick Sawtell" wrote:

> "Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
> news:29A4E586-F079-4471-8AB4-DF58E0D5E4E9@.microsoft.com...
> There are a couple of ways of handling this.
> 1. Does it have to be a space? I would suggest some other identifying
> mark like an asteriks (*) or a tilde(~). That way any TRIM type functions
> won't erase that space.
> 2. In the query itself, you can do something like this:
> SELECT col1, col2, '*' + SSNColumn
> FROM tablename
> 3. If that is undesirable, then you can always create a view with the above
> SELECT statement and then run DTS against the view rather than the base
> table.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>
|||Thanks Rick Sawtell,
The Second option worked perfectly.
SELECT col1, col2, ' ' + SSNColumn
FROM tablename
"Carlos Santos" wrote:
[vbcol=seagreen]
> So if I want to try this without the asteriks (*) or a tilde(~). I would
> type my query as: SELECT col1, col2, + SSNColumn
> FROM tablename
> Or: SELECT col1, col2, '' + SSNColumn
> FROM tablename
>
>
> "Rick Sawtell" wrote:
|||Carlos Santos wrote:
> Thanks Rick Sawtell,
> The Second option worked perfectly.
> SELECT col1, col2, ' ' + SSNColumn
> FROM tablename
> "Carlos Santos" wrote:
>
You may want to use SPACE(1) instead so it's clear in the proc what's
prefixing the string. You could easily add a second space and may not
realize it from the code. Some performance testing may be in order, but
I wouldn't expect that using the SPACE function would incurr much
overhead.
David Gugick
Imceda Software
www.imceda.com

Column space

Is there a command so that I can enter in my query to make a 1 space before
my results. The reason is that I'm currenlty running a report that gives me
patients demographics in a .CSV file and I used DTS to automate the process
the last information on each patient is SS# this file will then need to be
imported to to another vendor application. The problem is that the vendor
found a bug on their application that cuts the first digit on the SSN and in
order for them to fix their app. it will take months and I can't wait this
long. So what i'm trying to do is move the results on the last field (SSN#)
one space to the right so that the results on the .CSV file will be
,*111-11-1111
* blank space
Is this possible without manually editing the .CSV file?
Thanks,"Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
news:29A4E586-F079-4471-8AB4-DF58E0D5E4E9@.microsoft.com...
> Is there a command so that I can enter in my query to make a 1 space
> before
> my results. The reason is that I'm currenlty running a report that gives
> me
> patients demographics in a .CSV file and I used DTS to automate the
> process
> the last information on each patient is SS# this file will then need to be
> imported to to another vendor application. The problem is that the vendor
> found a bug on their application that cuts the first digit on the SSN and
> in
> order for them to fix their app. it will take months and I can't wait this
> long. So what i'm trying to do is move the results on the last field
> (SSN#)
> one space to the right so that the results on the .CSV file will be
> ,*111-11-1111
> * blank space
> Is this possible without manually editing the .CSV file?
> Thanks,
There are a couple of ways of handling this.
1. Does it have to be a space? I would suggest some other identifying
mark like an asteriks (*) or a tilde(~). That way any TRIM type functions
won't erase that space.
2. In the query itself, you can do something like this:
SELECT col1, col2, '*' + SSNColumn
FROM tablename
3. If that is undesirable, then you can always create a view with the above
SELECT statement and then run DTS against the view rather than the base
table.
Rick Sawtell
MCT, MCSD, MCDBA|||So if I want to try this without the asteriks (*) or a tilde(~). I would
type my query as: SELECT col1, col2, + SSNColumn
FROM tablename
Or: SELECT col1, col2, '' + SSNColumn
FROM tablename
"Rick Sawtell" wrote:
> "Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
> news:29A4E586-F079-4471-8AB4-DF58E0D5E4E9@.microsoft.com...
> > Is there a command so that I can enter in my query to make a 1 space
> > before
> > my results. The reason is that I'm currenlty running a report that gives
> > me
> > patients demographics in a .CSV file and I used DTS to automate the
> > process
> > the last information on each patient is SS# this file will then need to be
> > imported to to another vendor application. The problem is that the vendor
> > found a bug on their application that cuts the first digit on the SSN and
> > in
> > order for them to fix their app. it will take months and I can't wait this
> > long. So what i'm trying to do is move the results on the last field
> > (SSN#)
> > one space to the right so that the results on the .CSV file will be
> > ,*111-11-1111
> > * blank space
> >
> > Is this possible without manually editing the .CSV file?
> >
> > Thanks,
> There are a couple of ways of handling this.
> 1. Does it have to be a space? I would suggest some other identifying
> mark like an asteriks (*) or a tilde(~). That way any TRIM type functions
> won't erase that space.
> 2. In the query itself, you can do something like this:
> SELECT col1, col2, '*' + SSNColumn
> FROM tablename
> 3. If that is undesirable, then you can always create a view with the above
> SELECT statement and then run DTS against the view rather than the base
> table.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>|||Thanks Rick Sawtell,
The Second option worked perfectly.
SELECT col1, col2, ' ' + SSNColumn
FROM tablename
"Carlos Santos" wrote:
> So if I want to try this without the asteriks (*) or a tilde(~). I would
> type my query as: SELECT col1, col2, + SSNColumn
> FROM tablename
> Or: SELECT col1, col2, '' + SSNColumn
> FROM tablename
>
>
> "Rick Sawtell" wrote:
> >
> > "Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
> > news:29A4E586-F079-4471-8AB4-DF58E0D5E4E9@.microsoft.com...
> > > Is there a command so that I can enter in my query to make a 1 space
> > > before
> > > my results. The reason is that I'm currenlty running a report that gives
> > > me
> > > patients demographics in a .CSV file and I used DTS to automate the
> > > process
> > > the last information on each patient is SS# this file will then need to be
> > > imported to to another vendor application. The problem is that the vendor
> > > found a bug on their application that cuts the first digit on the SSN and
> > > in
> > > order for them to fix their app. it will take months and I can't wait this
> > > long. So what i'm trying to do is move the results on the last field
> > > (SSN#)
> > > one space to the right so that the results on the .CSV file will be
> > > ,*111-11-1111
> > > * blank space
> > >
> > > Is this possible without manually editing the .CSV file?
> > >
> > > Thanks,
> >
> > There are a couple of ways of handling this.
> >
> > 1. Does it have to be a space? I would suggest some other identifying
> > mark like an asteriks (*) or a tilde(~). That way any TRIM type functions
> > won't erase that space.
> >
> > 2. In the query itself, you can do something like this:
> > SELECT col1, col2, '*' + SSNColumn
> > FROM tablename
> >
> > 3. If that is undesirable, then you can always create a view with the above
> > SELECT statement and then run DTS against the view rather than the base
> > table.
> >
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
> >
> >|||Carlos Santos wrote:
> Thanks Rick Sawtell,
> The Second option worked perfectly.
> SELECT col1, col2, ' ' + SSNColumn
> FROM tablename
> "Carlos Santos" wrote:
>
You may want to use SPACE(1) instead so it's clear in the proc what's
prefixing the string. You could easily add a second space and may not
realize it from the code. Some performance testing may be in order, but
I wouldn't expect that using the SPACE function would incurr much
overhead.
--
David Gugick
Imceda Software
www.imceda.com