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)
