Cannot find a particular record by searching on non-primary key
in [Server Configuration]
|
From: gilak on 12 Jun 2010 05:23 On 11 June, 23:46, John Bell <jbellnewspo...(a)hotmail.com> wrote: > On Fri, 11 Jun 2010 07:23:04 -0700 (PDT), gilak <bkas...(a)gmail.com> > wrote: > > > > >John & Obirion > > >Thank you for your responses. Query select * from tbCustomer where > >CustomerID=ABCDE > >returns the correct record with no apparent white spaces, whereas > > >CustomerID=ABCDE > >Surname = Blogg > >Forename = Joe > > >Query select * from tblCustomer where Surname=Blogg and > >Forename=Joe returns no record . However after executing query: > > >Update tblCustomer set Surname=Blogg , Forename=Joe where > >CustomerID=ABCDE > > >and changing the name to the same forename and surname and then > >execution the query below: > > >select * from tblCustomer where Surname=Blogg and Forename=Joe > >returns the correct record. > > >John: Your suggested query: > > >'select * from tblCustomer where Surname='''+ surname + ''' and > >Forename='' + sorename + '''' > >from tblCustomer where CustomerID='ABCDEF' > > >returns Incorrect syntax near the keyword 'from'. Probably you > >meant the above to be 2 separate quries? > > >Obirion: your query works but it returns a customer with partially > >similar surname and forename > > >XBLOGGEX FGHKLM NULL NULL NULL BLOGGE JOES (please note the diffrence > >in surname=BLOGGE and Forename="JOES") > > >You query apparently suggets no white spaces. > > Looks like you missed out the first SELECT but there are a couple of > typos. It is a query that returns a SQL Statement that you can cut and > paste into the query window and run. Because you have to double up the > single quote to get it to appear in the string you end up with three > single quotes at the start/end of a string and 4 quotes for the single > quote in a string on it's own (if that makes sense?) > > SELECT > 'select * from tblCustomer where Surname='''+ surname + ''' and > Forename=''' + forename + '''' > from tblCustomer where CustomerID='ABCDEF' > > If your names fit a pattern then you could use LIKE and pattern > matching, and there are examples in Books Online or seehttp://msdn.microsoft.com/en-us/library/ms179859(SQL.100).aspx > > You may want to use SOUNDEX seehttp://msdn.microsoft.com/en-us/library/ms189282.aspx > > If this is not sufficient the Full Text Searching should offer more. > > John I have tried both LIKE and SOUNDEX and niether options return the record. I also run the following query suggested by Obiron: SELECT 'X'+ Surname + 'X' as 'SurnameCheck',* from tblCustomer where UPPER (Surname) like '%BLOGG%' and UPPER(Forename) like '%JOE%' but it did not return the correct record but another record with shared substrings in surname and forename similar surname and forename XBLOGGEX FGHKLM NULL NULL NULL BLOGGE JOES (please note the diffrence in surname=BLOGGE and Forename="JOES")
From: gilak on 12 Jun 2010 06:09
On 12 June, 10:23, gilak <bkas...(a)gmail.com> wrote: > On 11 June, 23:46, John Bell <jbellnewspo...(a)hotmail.com> wrote: > > > > > On Fri, 11 Jun 2010 07:23:04 -0700 (PDT), gilak <bkas...(a)gmail.com> > > wrote: > > > >John & Obirion > > > >Thank you for your responses. Query select * from tbCustomer where > > >CustomerID=ABCDE > > >returns the correct record with no apparent white spaces, whereas > > > >CustomerID=ABCDE > > >Surname = Blogg > > >Forename = Joe > > > >Query select * from tblCustomer where Surname=Blogg and > > >Forename=Joe returns no record . However after executing query: > > > >Update tblCustomer set Surname=Blogg , Forename=Joe where > > >CustomerID=ABCDE > > > >and changing the name to the same forename and surname and then > > >execution the query below: > > > >select * from tblCustomer where Surname=Blogg and Forename=Joe > > >returns the correct record. > > > >John: Your suggested query: > > > >'select * from tblCustomer where Surname='''+ surname + ''' and > > >Forename='' + sorename + '''' > > >from tblCustomer where CustomerID='ABCDEF' > > > >returns Incorrect syntax near the keyword 'from'. Probably you > > >meant the above to be 2 separate quries? > > > >Obirion: your query works but it returns a customer with partially > > >similar surname and forename > > > >XBLOGGEX FGHKLM NULL NULL NULL BLOGGE JOES (please note the diffrence > > >in surname=BLOGGE and Forename="JOES") > > > >You query apparently suggets no white spaces. > > > Looks like you missed out the first SELECT but there are a couple of > > typos. It is a query that returns a SQL Statement that you can cut and > > paste into the query window and run. Because you have to double up the > > single quote to get it to appear in the string you end up with three > > single quotes at the start/end of a string and 4 quotes for the single > > quote in a string on it's own (if that makes sense?) > > > SELECT > > 'select * from tblCustomer where Surname='''+ surname + ''' and > > Forename=''' + forename + '''' > > from tblCustomer where CustomerID='ABCDEF' > > > If your names fit a pattern then you could use LIKE and pattern > > matching, and there are examples in Books Online or seehttp://msdn.microsoft.com/en-us/library/ms179859(SQL.100).aspx > > > You may want to use SOUNDEX seehttp://msdn.microsoft.com/en-us/library/ms189282.aspx > > > If this is not sufficient the Full Text Searching should offer more. > > > John > > I have tried both LIKE and SOUNDEX and niether options return the > record. I also run > the following query suggested by Obiron: > > SELECT 'X'+ Surname + 'X' as 'SurnameCheck',* from tblCustomer where > UPPER > (Surname) like '%BLOGG%' and UPPER(Forename) like '%JOE%' > > but it did not return the correct record but another record with > shared substrings in surname and forename > similar surname and forename > > XBLOGGEX FGHKLM NULL NULL NULL BLOGGE JOES > > (please note the diffrence in surname=BLOGGE and Forename="JOES") Thanks for all your help Guys. Problem solved! I am sorry to have wasted your time. This was a simple case of a user swapping Surname with forename at the data entry. I and a one of my other colleague are deeply embarrassed in not spotting this simple user error! |