|
|
You're in the
sql server full text search
group:Searching accros multiple columns
sql server full text search:
Can someone please help me
I have one table which I need to search multiple columns on The table looks similar to thi id Category Descriptio -------------------------------------------------------------------------- 0 Gardening Equipment Rak 1 Gardening Equipmen Spad I need to be able to search on 'Garden AND rake so that the first row comes up Currently I have made on a Full-Text catalogue on this table using the two tables 'Category' and 'Description and written the following stored procedur CREATE PROCEDURE dbo.sp01_searchRequisite @searchString varchar(50 A SELECT Category, Descriptio FROM pgT001_RequisiteCatalogu WHERE CONTAINS(*, @searchString G This returns 0 results Any ideas or suggestions are more than welcome Many thank
RP,
You're statements are a bit confusing... Are you "search multiple columns" or "two tables 'Category' and 'Description'? I think you're miss-understanding the use of CONTAINS in your stored proc sp01_searchRequisite. Your FROM clause should be from one or both of your FT-enable tables ('Category' and 'Description' ?) and not, I'm assuming the FT Catalog name: pgT001_RequisiteCatalogue. Assuming one table (pgT001_RequisiteCatalogue) and multiple columns, you could do the following (example from the northwind database and tables) and using CONTAINSTABLE: SELECT e.LastName, e.FirstName, e.Title, e.Notes from Employees AS e, containstable(Employees, Notes, 'BA') as A, containstable(Employees, Title, 'Sales') as B where A.[KEY] = e.EmployeeID and B.[KEY] = e.EmployeeID Assuming mutiple tables and therefore multiple FT-enable columns (one column from each table), you could do the following: SELECT e.LastName, e.FirstName, e.Title, e.Notes t.TerritoryID from Employees AS e, EmployeeTerritories t, containstable(Employees, Notes, 'BA') as A, containstable(EmployeeTerritories, TerritoryID, 'Sales') as B where A.[KEY] = e.EmployeeID and B.[KEY] = e.EmployeeID Note, in order for this example to work, you must alter the table EmployeeTerritories and add a single non-null column in order to use as the FT-Index key. If this is not what you're looking for, could you post a more precise example of what you're looking for? Thanks, John [quoted text, click to view] "RoastedPigeon" <julian.patmore@hertscc.gov.uk> wrote in message news:29A65A8C-D74F-4EC6-AD1B-6CBFF1CE7CCA@microsoft.com... > Can someone please help me? > > I have one table which I need to search multiple columns on. > The table looks similar to this > > id Category Description > -------------------------------------------------------------------------- - > 0 Gardening Equipment Rake > 1 Gardening Equipmen Spade > > I need to be able to search on 'Garden AND rake' > so that the first row comes up. > > Currently I have made on a Full-Text catalogue on this table using the two tables 'Category' and 'Description' > > and written the following stored procedure > > CREATE PROCEDURE dbo.sp01_searchRequisite > @searchString varchar(50) > > AS > > SELECT Category, Description > FROM pgT001_RequisiteCatalogue > WHERE CONTAINS(*, @searchString) > > GO > > This returns 0 results. > > Any ideas or suggestions are more than welcome. > > Many thanks > > RP
Sorry about the confusion and thank you for your reply
I did actually mean from 2 columns in one table Thank you for your suggestion although it does not quite help me, as I do not know that the first word in the search string will be from one column and second word from the other. Also someone could have n umber of words in the search string Is there another way rather than merging the columns into one column, as this would work Thanks in advance R ----- John Kane wrote: ---- RP You're statements are a bit confusing... Are you "search multiple columns or "two tables 'Category' and 'Description' I think you're miss-understanding the use of CONTAINS in your stored pro sp01_searchRequisite. Your FROM clause should be from one or both of you FT-enable tables ('Category' and 'Description' ?) and not, I'm assuming th FT Catalog name: pgT001_RequisiteCatalogue Assuming one table (pgT001_RequisiteCatalogue) and multiple columns, yo could do the following (example from the northwind database and tables) an using CONTAINSTABLE SELECT e.LastName, e.FirstName, e.Title, e.Note from Employees AS e containstable(Employees, Notes, 'BA') as A containstable(Employees, Title, 'Sales') as wher A.[KEY] = e.EmployeeID an B.[KEY] = e.EmployeeI Assuming mutiple tables and therefore multiple FT-enable columns (one colum from each table), you could do the following SELECT e.LastName, e.FirstName, e.Title, e.Notes t.TerritoryI from Employees AS e, EmployeeTerritories t containstable(Employees, Notes, 'BA') as A containstable(EmployeeTerritories, TerritoryID, 'Sales') as wher A.[KEY] = e.EmployeeID an B.[KEY] = e.EmployeeI Note, in order for this example to work, you must alter the tabl EmployeeTerritories and add a single non-null column in order to use as th FT-Index key. If this is not what you're looking for, could you post a mor precise example of what you're looking for Thanks Joh "RoastedPigeon" <julian.patmore@hertscc.gov.uk> wrote in messag news:29A65A8C-D74F-4EC6-AD1B-6CBFF1CE7CCA@microsoft.com.. [quoted text, click to view] > Can someone please help me >> I have one table which I need to search multiple columns on > The table looks similar to thi >> id Category Descriptio > ------------------------------------------------------------------------- > 0 Gardening Equipment Rak > 1 Gardening Equipmen Spad >> I need to be able to search on 'Garden AND rake > so that the first row comes up >> Currently I have made on a Full-Text catalogue on this table using the tw tables 'Category' and 'Description >> and written the following stored procedur >> CREATE PROCEDURE dbo.sp01_searchRequisit > @searchString varchar(50 >> A >> SELECT Category, Descriptio > FROM pgT001_RequisiteCatalogu > WHERE CONTAINS(*, @searchString >> G >> This returns 0 results >> Any ideas or suggestions are more than welcome >> Many thank >> R
You're welcome, RP,
Not to worry, I basiclly understand what you're looking for, but just need to clarify some things. What you're really looking for is "across-column" Full-text Search (FTS) using SQL Server 2000. Unfortunely, SQL Server 2000 does not support this and SQL Server 7.0 was "fixed" in SP2 to comply with the SQL 2000 default behievor. I'd post the related KB article, but I'm out of town and don't have access to my KB lists... Yes, you can merge or concat the two column into one column and then FT Index that one column, but this might not be best for your application design. This issue may or may not be fixed in a future release of SQL Server or perhaps even in a futre SP for SQL Server 2000.... Best regards, John [quoted text, click to view] "RoastedPigeon" <anonymous@discussions.microsoft.com> wrote in message news:<C52076BC-537C-4FE4-BED6-2920967C3383@microsoft.com>...
> Sorry about the confusion and thank you for your reply. > > I did actually mean from 2 columns in one table. > > Thank you for your suggestion although it does not quite help me, as I do not know that the first word in the search string will be from one column and second word from the other. Also someone could have n umber of words in the search string. > > Is there another way rather than merging the columns into one column, as this would work. > > Thanks in advance > > RP > > > > ----- John Kane wrote: ----- > > RP, > You're statements are a bit confusing... Are you "search multiple columns" > or "two tables 'Category' and 'Description'? > > I think you're miss-understanding the use of CONTAINS in your stored proc > sp01_searchRequisite. Your FROM clause should be from one or both of your > FT-enable tables ('Category' and 'Description' ?) and not, I'm assuming the > FT Catalog name: pgT001_RequisiteCatalogue. > > Assuming one table (pgT001_RequisiteCatalogue) and multiple columns, you > could do the following (example from the northwind database and tables) and > using CONTAINSTABLE: > > SELECT e.LastName, e.FirstName, e.Title, e.Notes > from Employees AS e, > containstable(Employees, Notes, 'BA') as A, > containstable(Employees, Title, 'Sales') as B > where > A.[KEY] = e.EmployeeID and > B.[KEY] = e.EmployeeID > > > Assuming mutiple tables and therefore multiple FT-enable columns (one column > from each table), you could do the following: > > SELECT e.LastName, e.FirstName, e.Title, e.Notes t.TerritoryID > from Employees AS e, EmployeeTerritories t, > containstable(Employees, Notes, 'BA') as A, > containstable(EmployeeTerritories, TerritoryID, 'Sales') as B > where > A.[KEY] = e.EmployeeID and > B.[KEY] = e.EmployeeID > > Note, in order for this example to work, you must alter the table > EmployeeTerritories and add a single non-null column in order to use as the > FT-Index key. If this is not what you're looking for, could you post a more > precise example of what you're looking for? > > Thanks, > John > > > "RoastedPigeon" <julian.patmore@hertscc.gov.uk> wrote in message > news:29A65A8C-D74F-4EC6-AD1B-6CBFF1CE7CCA@microsoft.com... > > Can someone please help me? > >> I have one table which I need to search multiple columns on. > > The table looks similar to this > >> id Category Description > > -------------------------------------------------------------------------- > - > > 0 Gardening Equipment Rake > > 1 Gardening Equipmen Spade > >> I need to be able to search on 'Garden AND rake' > > so that the first row comes up. > >> Currently I have made on a Full-Text catalogue on this table using the two > tables 'Category' and 'Description' > >> and written the following stored procedure > >> CREATE PROCEDURE dbo.sp01_searchRequisite > > @searchString varchar(50) > >> AS > >> SELECT Category, Description > > FROM pgT001_RequisiteCatalogue > > WHERE CONTAINS(*, @searchString) > >> GO > >> This returns 0 results. > >> Any ideas or suggestions are more than welcome. > >> Many thanks
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |