-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I think I understand the problem.
The problem is in the AND / OR statements. The problem is the query would
evaluate the first AND pair, then
try to match on the last OR
Try this:
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
- -alan
- ----- Original Message -----
From: Rob Matlow <[EMAIL PROTECTED]>
To: CFTALK <[EMAIL PROTECTED]>
Sent: Tuesday, March 05, 2002 4:51 PM
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)
>
>
-----BEGIN PGP SIGNATURE-----
Version: PGP Personal Security 7.0.3
iQA/AwUBPIVq4iy8dBS/RM9kEQIWIgCfThF8f4F03F7Eug6hqP/mMTARwOIAnik8
riApEOwHexVgCsULwhA7vBs4
=a+LG
-----END PGP SIGNATURE-----
-
You are subscribed to the CFUGToronto CFTALK ListSRV.
This message has been posted by: "Alan Goldberg" <[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)
