yeah, it's ben a long day - that's what I meant.... not enough sleep lately. Do you mean that when you enter text in the search box, you get back a record for each user * the number of companies?
ie John Smith Company 1 John Doe Company 1 Jonh Hancock Company 1 John Smith Company 2 John Doe Company 2 Jonh Hancock Company 2 John Smith Company 3 John Doe Company 3 Jonh Hancock Company 3 So let me see if I've got this straight - when you don't enter anything (to do an "all") search it returns all the contact names but associated with every company in your company table, as shown above. But when you actually enter search criteria, such as "John" for example, the search returns properly? Are you only trying to find one contact at a time or are you trying to find multiple contacts? As I'm looking at the sql right now it seems like you should be, at worst, getting back a duplicate set not one for each company. And it still seems like the brackets are in the wrong place.... Should be: AND (contacts.firstName LIKE '#trim(search)#' OR contacts.lastName LIKE '#trim(search)#') Not: AND (contacts.firstName LIKE '#trim(search)#') OR (contacts.lastName LIKE '#trim(search)#') Like this it seems to me, and I could be wrong, that it's no different than not having the brackets at all. Unfortunately I'm not at my testing box right now so I can't really replicate your error. I'm left rolling it over in my sleep-lacking head. BTW I'm not as dim as I sound right now - I'll figure it out eventually :-) Anyone else have any thoughts? Karl On Tuesday, March 5, 2002, at 06:06 PM, Rob Matlow wrote: > Well actually if you were to put in an cfif statement (which you are > right > about) it should be <cfif form.search is not "">. It form.search > always be > defined if you are coming from the form. As I mention the problem > seems to > be using the % wildcard. It works fine without it but the user can't > be as > vague in what they type. It must be a valid first name or last name to > pull > back a record. Does anyone have any other suggestions? > > Thanks, > > > Rob > > <CFQUERY NAME="getContact" DATASOURCE="#DSN#"> > SELECT contacts.contactID, contacts.firstName, contacts.lastName, > company.companyName > FROM contacts, company > WHERE company.companyID = contacts.companyID > <cfif form.search is not ""> > AND (contacts.firstName LIKE '#trim(search)#') > OR (contacts.lastName LIKE '#trim(search)#') > </cfif> > ORDER BY contacts.lastName, contacts.firstName > </CFQUERY> > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On > Behalf Of Karl Zarudny > Sent: March 5, 2002 5:12 PM > To: [EMAIL PROTECTED] > Subject: Re: [CFTALKTor] SQL question? > > > Sorry I read that wrong. Wouldn't you simply wrap an IF around the items > after the company.companyid = contacts.contactid? > > i.e. > <CFQUERY NAME="getContact" DATASOURCE="#DSN#"> > SELECT contacts.contactID, contacts.firstName, contacts.lastName, > company.companyName > FROM contacts, company > WHERE company.companyID = contacts.companyID > <cfif IsDefined("FORM.search")> > AND (contacts.firstName LIKE '#trim(Form.search)#%' > OR contacts.lastName LIKE '#trim(Form.search)#%') > </cfif> > ORDER BY contacts.lastName, contacts.firstName > </CFQUERY> > >> From: Karl Zarudny <[EMAIL PROTECTED]> >> Reply-To: [EMAIL PROTECTED] >> Date: Tue, 05 Mar 2002 17:06:14 -0500 >> To: <[EMAIL PROTECTED]> >> Subject: Re: [CFTALKTor] SQL question? >> >> I may be mistaken but shouldn't there be brackets around the OR clause? >> >> <CFQUERY NAME="getContact" DATASOURCE="#DSN#"> >> SELECT contacts.contactID, contacts.firstName, contacts.lastName, >> company.companyName >> FROM contacts, company >> WHERE company.companyID = contacts.companyID >> AND (contacts.firstName LIKE '#trim(Form.search)#%' >> OR contacts.lastName LIKE '#trim(Form.search)#%') >> ORDER BY contacts.lastName, contacts.firstName >> </CFQUERY> >> >> >>> From: "Rob Matlow" <[EMAIL PROTECTED]> >>> Reply-To: [EMAIL PROTECTED] >>> Date: Tue, 5 Mar 2002 16:51:47 -0500 >>> To: "CFTALK" <[EMAIL PROTECTED]> >>> Subject: [CFTALKTor] SQL question? >>> >>> >>> I am banging my head trying to get this simple search interface to >>> work > and >>> I'm just not getting back what I expect. I have a textbox where the >>> user >>> can type in a person's name (contact) first or last and it should >>> search > the >>> firstName field and lastName field in the database. When I search on > "all" >>> it returns the proper contacts but associated with ever company >>> entered. >>> When I fill in something it returns the correct information. Any >>> ideas? >>> Hope this made sense. >>> >>> >>> <CFQUERY NAME="getContact" DATASOURCE="#DSN#"> >>> SELECT contacts.contactID, contacts.firstName, contacts.lastName, >>> company.companyName >>> FROM contacts, company >>> WHERE company.companyID = contacts.companyID >>> AND contacts.firstName LIKE '#trim(Form.search)#%' >>> OR contacts.lastName LIKE '#trim(Form.search)#%' >>> ORDER BY contacts.lastName, contacts.firstName >>> </CFQUERY> >>> >>> >>> Thanks, >>> >>> Rob >>> >>> >>> - >>> You are subscribed to the CFUGToronto CFTALK ListSRV. >>> This message has been posted by: "Rob Matlow" > <[EMAIL PROTECTED]> >>> To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ >>> Manager: Kevin Towes ([EMAIL PROTECTED]) > http://www.CFUGToronto.org/ >>> This System has been donated by Infopreneur, Inc. >>> (http://www.infopreneur.net) >> >> - >> You are subscribed to the CFUGToronto CFTALK ListSRV. >> This message has been posted by: Karl Zarudny >> <[EMAIL PROTECTED]> >> To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ >> Manager: Kevin Towes ([EMAIL PROTECTED]) > http://www.CFUGToronto.org/ >> This System has been donated by Infopreneur, Inc. >> (http://www.infopreneur.net) > > - > You are subscribed to the CFUGToronto CFTALK ListSRV. > This message has been posted by: Karl Zarudny > <[EMAIL PROTECTED]> > To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ > Manager: Kevin Towes ([EMAIL PROTECTED]) > http://www.CFUGToronto.org/ > This System has been donated by Infopreneur, Inc. > (http://www.infopreneur.net) > > > > - > You are subscribed to the CFUGToronto CFTALK ListSRV. > This message has been posted by: "Rob Matlow" > <[EMAIL PROTECTED]> > To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ > Manager: Kevin Towes ([EMAIL PROTECTED]) > http://www.CFUGToronto.org/ > This System has been donated by Infopreneur, Inc. > (http://www.infopreneur.net) > - You are subscribed to the CFUGToronto CFTALK ListSRV. This message has been posted by: Karl Zarudny <[EMAIL PROTECTED]> To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ This System has been donated by Infopreneur, Inc. (http://www.infopreneur.net)
