I'm thinking the index is just not finished being built and this is causing issues. i'm going to give it a day (or weekend as the case may be) to catch up and settle down... thanks for your efforts though teddy! ___________________ Eric Jones aka jonese http://www.jonese.us http://twitter.com/jonese
On Fri, Jan 23, 2009 at 4:18 PM, Teddy R. Payne <teddyrpa...@gmail.com>wrote: > And this: > > SELECT > * > FROM > ( > SELECT > * > FROM > author > WHERE > CONTAINS(authorLastName, N'"LYNE"') > ) q1 > WHERE > CONTAINS(authorFirstName, N'"B"') > > > Teddy R. Payne, ACCFD > Google Talk - teddyrpa...@gmail.com > > > > On Fri, Jan 23, 2009 at 4:11 PM, jonese <gig...@gmail.com> wrote: > >> Still returns zero records. >> if i do >> select * from author >> WHERE CONTAINS(authorLastName, N'"LYNE"') >> i get 6 records, one of those has a forename of B >> >> If i do >> select * >> from author >> WHERE CONTAINS(authorForeName, N'"B"') >> i get zero records >> >> If I do >> select * >> from author >> WHERE CONTAINS(authorForeName, N'"*B*"') >> >> I get 5 results but none of them are just forename of B >> >> wierd..... >> ___________________ >> Eric Jones aka jonese >> http://www.jonese.us >> http://twitter.com/jonese >> >> >> On Fri, Jan 23, 2009 at 4:05 PM, Teddy R. Payne >> <teddyrpa...@gmail.com>wrote: >> >>> Ok, try this then if you are using SQL Server: >>> >>> USE WhateverYourDatabaseIs; >>> GO >>> >>> DECLARE >>> @foreName nvarchar(100) >>> , @lastName nvarchar(100) >>> >>> SELECT >>> @foreName = N'B' >>> , @lastName = N'Lyne' >>> >>> SELECT >>> * >>> FROM >>> author >>> WHERE >>> CONTAINS(authorForeName, @foreName) AND CONTAINS(authorLastName, >>> @lastName) >>> >>> >>> >>> Teddy R. Payne, ACCFD >>> Google Talk - teddyrpa...@gmail.com >>> >>> >>> >>> On Fri, Jan 23, 2009 at 3:51 PM, jonese <gig...@gmail.com> wrote: >>> >>>> Msg 102, Level 15, State 1, Line 3 >>>> Incorrect syntax near 'N'B''. >>>> ___________________ >>>> Eric Jones aka jonese >>>> http://www.jonese.us >>>> http://twitter.com/jonese >>>> >>>> >>>> On Fri, Jan 23, 2009 at 3:48 PM, Teddy R. Payne < >>>> teddyrpa...@gmail.com> wrote: >>>> >>>>> SELECT * >>>>> FROM author >>>>> WHERE CONTAINS(authorForeName, "N'B'") AND CONTAINS(authorLastName, >>>>> "N'LYNE'") >>>>> >>>>> Did you try the above query? Which is a slight derivation on yours. >>>>> >>>>> Teddy R. Payne, ACCFD >>>>> Google Talk - teddyrpa...@gmail.com >>>>> >>>>> >>>>> >>>>> On Fri, Jan 23, 2009 at 3:42 PM, jonese <gig...@gmail.com> wrote: >>>>> >>>>>> Teddy, >>>>>> Nope the quotes are all ok. the value is in single quotes after the N >>>>>> (yes it's nVarChar) and the double quote are for phrases. In my example i >>>>>> could have removed the double quotes and gotten the same results etc. >>>>>> >>>>>> The syntax appears to be A-OK because i get no errors. I'm just not >>>>>> getting why i can't search on MULTIPLE columns with MULTIPLE values which >>>>>> are in a fulltext index.. >>>>>> ___________________ >>>>>> Eric Jones aka jonese >>>>>> http://www.jonese.us >>>>>> http://twitter.com/jonese >>>>>> >>>>>> >>>>>> On Fri, Jan 23, 2009 at 3:36 PM, Teddy R. Payne < >>>>>> teddyrpa...@gmail.com> wrote: >>>>>> >>>>>>> What is the data type of the columns you are referencing? >>>>>>> >>>>>>> (authorForeName, N'"B"') The N there I believe is a reference for >>>>>>> nVarchar if you are allowing i10n compatibility. >>>>>>> >>>>>>> The syntax looks wonky as I thought it was only a single quote like >>>>>>> N'B' >>>>>>> >>>>>>> Then you can wrap it up in double quotes like "N'B'" >>>>>>> >>>>>>> So I suspect you quotes are out of order. Double quote then the N >>>>>>> then the single quote then the literal string then single quote then >>>>>>> double >>>>>>> quote. Savvy? >>>>>>> >>>>>>> Teddy R. Payne, ACCFD >>>>>>> Google Talk - teddyrpa...@gmail.com >>>>>>> >>>>>>> >>>>>>> >>>>>>> On Fri, Jan 23, 2009 at 3:04 PM, jonese <gig...@gmail.com> wrote: >>>>>>> >>>>>>>> I have two columns authorForeNAme and authorLastName and i'm trying >>>>>>>> to search against a Full Text index to find a specific author. >>>>>>>> i thought i could do this: >>>>>>>> >>>>>>>> SELECT * >>>>>>>> FROM author >>>>>>>> WHERE CONTAINS(authorForeName, N'"B"') AND CONTAINS(authorLastName, >>>>>>>> N'"LYNE"') >>>>>>>> >>>>>>>> But it returns zero records. however if i do >>>>>>>> >>>>>>>> select * >>>>>>>> from author >>>>>>>> WHERE authorForeName = 'B'' AND CONTAINS(authorLastName, N'"LYNE"') >>>>>>>> >>>>>>>> I get my desired results. >>>>>>>> >>>>>>>> Why doesn't the first query work as expected? >>>>>>>> ___________________ >>>>>>>> Eric Jones aka jonese >>>>>>>> http://www.jonese.us >>>>>>>> http://twitter.com/jonese >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>> >>>> >>> >> >