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

Combining a Stacked column and continous line graph

Hi All
I am trying a build a RS report which as a graph which combines two
series in to a stacked column and the remaining 6 data series as
continous line graphs.
I sort of managed to get it but it never works when compared to the
corresponding Excel graph.
Please help how do I build a graph report with combination of stacked
column and continous line graphs.
Thanks
KarenYou can combine a column and a line chart. Just set the chart type to column
and look for the "Plot data as line" checkbox on the data value appearance
tab. You may also be interested in this "how to" article:
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B842422
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
<karenmiddleol@.yahoo.com> wrote in message
news:1131153902.660326.3140@.g44g2000cwa.googlegroups.com...
> Hi All
> I am trying a build a RS report which as a graph which combines two
> series in to a stacked column and the remaining 6 data series as
> continous line graphs.
>
> I sort of managed to get it but it never works when compared to the
> corresponding Excel graph.
>
> Please help how do I build a graph report with combination of stacked
> column and continous line graphs.
> Thanks
> Karen
>

Collations

Hi,
I'm starting to build a database that will contain English and Welsh
text. Up until now I have just used the default collation settings, and
everything has been fine - but I've only ever needed to use English.
There is also the possibility that, at some point in the future, the
database will have to handle several Arabic and Asian languages.
So, my question is what collation should I used to make the database as
future-proof as possible given the statements above? Does, for example,
the default collation actually support the Welsh alphabet? (Welsh has
four extra characters that I don't think appear in other European
languages - ?, ? and their upper case equivalents - possibly more, but I
don't actually know Welsh!) I notice that there isn't a bog-standard
"UTF-8" option in the collation list...
Lastly, does anyone have any general tips regarding multi-lingual databases?
Cheers,
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk
The opinions stated above are not necessarily representative of
those of my cats. All opinions expressed are entirely your own.Hi Dylan,
Basically you collation should be the local environment where it is. So if
its in the UK it should be english etc.
With regards to your text, what you need to do is have the appropriate
datatype's for your tables.
For storing text that you would want as not only english, but another
language, instead of using a datatype of say char(10) you would use
nchar(10) this N, ensures that the type is unicode and not ANSI. Text
conversions of char can have mistakes, yet nchar represents the characters
as they should. One important note, using nchar instead of char, will
consume twice as much space as char, so just something to bear in mind.
hth
Barry Andrew
"Dylan Parry" <usenet@.dylanparry.com> wrote in message
news:460d0c87$0$761$bed64819@.news.gradwell.net...
> Hi,
> I'm starting to build a database that will contain English and Welsh
> text. Up until now I have just used the default collation settings, and
> everything has been fine - but I've only ever needed to use English.
> There is also the possibility that, at some point in the future, the
> database will have to handle several Arabic and Asian languages.
> So, my question is what collation should I used to make the database as
> future-proof as possible given the statements above? Does, for example,
> the default collation actually support the Welsh alphabet? (Welsh has
> four extra characters that I don't think appear in other European
> languages - ?, ? and their upper case equivalents - possibly more, but I
> don't actually know Welsh!) I notice that there isn't a bog-standard
> "UTF-8" option in the collation list...
> Lastly, does anyone have any general tips regarding multi-lingual
> databases?
> Cheers,
> --
> Dylan Parry
> http://electricfreedom.org | http://webpageworkshop.co.uk
> The opinions stated above are not necessarily representative of
> those of my cats. All opinions expressed are entirely your own.|||Barry Andrew Hall wrote:
> Hi Dylan,
> Basically you collation should be the local environment where it is.
> So if its in the UK it should be english etc.
Ah okay - so I should simply leave it "as is", right?

> With regards to your text, what you need to do is have the
> appropriate datatype's for your tables.
> For storing text that you would want as not only english, but another
> language, instead of using a datatype of say char(10) you would use
> nchar(10) this N, ensures that the type is unicode and not ANSI.
> Text conversions of char can have mistakes, yet nchar represents the
> characters as they should.
Right, I understand now. So instead of using "text" to store several
paragraphs of writing, I should use "ntext" - but only in places that
could contain Welsh text.

> One important note, using nchar instead of char, will consume twice
> as much space as char, so just something to bear in mind.
In much the same way as saving a text file in UTF-8 instead of
ISO-8859-1 will consume more space. That's not too much of an issue for
me, although I guess it could cause degradation in performance?
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk
The opinions stated above are not necessarily representative of
those of my cats. All opinions expressed are entirely your own.|||Your right on the performance. But to be fair, if its designed solidly you
shouldnt have a thing to worry about.
Also, as you said, anything you want potentially in another language, have a
datatype of nvarchar(max) thats probably your best bet.
"Dylan Parry" <usenet@.dylanparry.com> wrote in message
news:460d1330$0$757$bed64819@.news.gradwell.net...
> Barry Andrew Hall wrote:
> Ah okay - so I should simply leave it "as is", right?
>
> Right, I understand now. So instead of using "text" to store several
> paragraphs of writing, I should use "ntext" - but only in places that
> could contain Welsh text.
>
> In much the same way as saving a text file in UTF-8 instead of
> ISO-8859-1 will consume more space. That's not too much of an issue for
> me, although I guess it could cause degradation in performance?
> --
> Dylan Parry
> http://electricfreedom.org | http://webpageworkshop.co.uk
> The opinions stated above are not necessarily representative of
> those of my cats. All opinions expressed are entirely your own.

Collations

Hi,
I'm starting to build a database that will contain English and Welsh
text. Up until now I have just used the default collation settings, and
everything has been fine - but I've only ever needed to use English.
There is also the possibility that, at some point in the future, the
database will have to handle several Arabic and Asian languages.
So, my question is what collation should I used to make the database as
future-proof as possible given the statements above? Does, for example,
the default collation actually support the Welsh alphabet? (Welsh has
four extra characters that I don't think appear in other European
languages - ŵ, ŷ and their upper case equivalents - possibly more, but I
don't actually know Welsh!) I notice that there isn't a bog-standard
"UTF-8" option in the collation list...
Lastly, does anyone have any general tips regarding multi-lingual databases?
Cheers,
--
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk
The opinions stated above are not necessarily representative of
those of my cats. All opinions expressed are entirely your own.Hi Dylan,
Basically you collation should be the local environment where it is. So if
its in the UK it should be english etc.
With regards to your text, what you need to do is have the appropriate
datatype's for your tables.
For storing text that you would want as not only english, but another
language, instead of using a datatype of say char(10) you would use
nchar(10) this N, ensures that the type is unicode and not ANSI. Text
conversions of char can have mistakes, yet nchar represents the characters
as they should. One important note, using nchar instead of char, will
consume twice as much space as char, so just something to bear in mind.
hth
Barry Andrew
"Dylan Parry" <usenet@.dylanparry.com> wrote in message
news:460d0c87$0$761$bed64819@.news.gradwell.net...
> Hi,
> I'm starting to build a database that will contain English and Welsh
> text. Up until now I have just used the default collation settings, and
> everything has been fine - but I've only ever needed to use English.
> There is also the possibility that, at some point in the future, the
> database will have to handle several Arabic and Asian languages.
> So, my question is what collation should I used to make the database as
> future-proof as possible given the statements above? Does, for example,
> the default collation actually support the Welsh alphabet? (Welsh has
> four extra characters that I don't think appear in other European
> languages - ŵ, ŷ and their upper case equivalents - possibly more, but I
> don't actually know Welsh!) I notice that there isn't a bog-standard
> "UTF-8" option in the collation list...
> Lastly, does anyone have any general tips regarding multi-lingual
> databases?
> Cheers,
> --
> Dylan Parry
> http://electricfreedom.org | http://webpageworkshop.co.uk
> The opinions stated above are not necessarily representative of
> those of my cats. All opinions expressed are entirely your own.|||Barry Andrew Hall wrote:
> Hi Dylan,
> Basically you collation should be the local environment where it is.
> So if its in the UK it should be english etc.
Ah okay - so I should simply leave it "as is", right?
> With regards to your text, what you need to do is have the
> appropriate datatype's for your tables.
> For storing text that you would want as not only english, but another
> language, instead of using a datatype of say char(10) you would use
> nchar(10) this N, ensures that the type is unicode and not ANSI.
> Text conversions of char can have mistakes, yet nchar represents the
> characters as they should.
Right, I understand now. So instead of using "text" to store several
paragraphs of writing, I should use "ntext" - but only in places that
could contain Welsh text.
> One important note, using nchar instead of char, will consume twice
> as much space as char, so just something to bear in mind.
In much the same way as saving a text file in UTF-8 instead of
ISO-8859-1 will consume more space. That's not too much of an issue for
me, although I guess it could cause degradation in performance?
--
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk
The opinions stated above are not necessarily representative of
those of my cats. All opinions expressed are entirely your own.|||Your right on the performance. But to be fair, if its designed solidly you
shouldnt have a thing to worry about.
Also, as you said, anything you want potentially in another language, have a
datatype of nvarchar(max) thats probably your best bet.
"Dylan Parry" <usenet@.dylanparry.com> wrote in message
news:460d1330$0$757$bed64819@.news.gradwell.net...
> Barry Andrew Hall wrote:
>> Hi Dylan,
>> Basically you collation should be the local environment where it is.
>> So if its in the UK it should be english etc.
> Ah okay - so I should simply leave it "as is", right?
>> With regards to your text, what you need to do is have the
>> appropriate datatype's for your tables.
>> For storing text that you would want as not only english, but another
>> language, instead of using a datatype of say char(10) you would use
>> nchar(10) this N, ensures that the type is unicode and not ANSI.
>> Text conversions of char can have mistakes, yet nchar represents the
>> characters as they should.
> Right, I understand now. So instead of using "text" to store several
> paragraphs of writing, I should use "ntext" - but only in places that
> could contain Welsh text.
>> One important note, using nchar instead of char, will consume twice
>> as much space as char, so just something to bear in mind.
> In much the same way as saving a text file in UTF-8 instead of
> ISO-8859-1 will consume more space. That's not too much of an issue for
> me, although I guess it could cause degradation in performance?
> --
> Dylan Parry
> http://electricfreedom.org | http://webpageworkshop.co.uk
> The opinions stated above are not necessarily representative of
> those of my cats. All opinions expressed are entirely your own.