LIKE

[05082000]

I NEED TO DO A QUERY IN WHICH I HAVE TO DO ONE SEARCH BY TWO DIFFERENT FIELD FIRST NAME OR LAST NAME. HERE IS THE QUERY.

<cfquery name=&quot;con&quot; datasource=&quot;ltw&quot; dbtype=&quot;ODBC&quot;>

SELECT FIRST, LAST, FAXPHONE
FROM CUSTOMER

WHERE FIRST LIKE '%#FIRST#%'OR LAST LIKE '%#LIKE#%'

</cfquery>
I NEEDED IF I WANT TO DO SEARCH ON LAST NAME INSTEAD OF FIRST THE RESULT STILL DISPLAYS.

 

[dfwalton]

When I run a similar query:

SELECT first, last from customer
WHERE first LIKE '%Rob%' OR last LIKE '%wil%&quot;

I get the following result set:
First Last
Rob Levy
Robert Shapiro
Michael Wilson
Kevin Wilkerson


I believe you are saying that &quot;what if I only want the last name match&quot;. I think in that case you need two queries. One for the First, second for the Last. If you also need a true OR, then you could do a union between the first two. If you are constructing the SQL dynamically, this shouldn't be too hard to do:

<cfif len(trim(#first#) GT 0>
Select First, Last, Fax
from Customer
where first like '%#first#%'
</cfif>
<cfif len(trim(#last#) GT 0 AND len(trim(#first#) GT 0>
UNION
</cfif>
<cfif len(trim(#last#) GT 0>
Select First, Last, Fax
from Customer
where first like '%#Last#%'
</cfif>

This way, if you populate the #first# field and not the #last#, you will only select on Like #first#, and vice versa. If you populate both #first# and #last#, you'll bet the entire list.

David

 

[alltheverses]

May I suggest:

Select FirstName, LastName, FaxPhone
  from Customer
 where 0=0
   and <cfif len(form.fname) or len(form.lname)>
       (<cfif len(form.fname)>
         FirstName like '%#form.fname#%'</cfif>
        <cfif len(form.fname) and len(form.lname)>
         or </cfif>
        <cfif len(form.lname)>LastName like '%
         #form.lname#%'</cfif>)</cfif>

You'll notice three things.

. where 0=0... An easy base where clause that's always true so you don't need a whole bunch of cfifs to know if its ok for cfif to be there.
. I changed your field names.. Just a suggestion... seems first and last might cause some issues with some query languages.
. the ( before the second cfif and the ) after the second-to-last /cfif: That isolates that to making sure either of those are true or both are true so that if you add other criteria, you can add it outside for you could add &quot;and isActive=1&quot; after the parenthese. and require either a firstname or lastname match or both to match, but always have isActive=1... Without the () the or statement would throw it off.

 

[r937]

alltheverses, nice job, especially the 0=0 trick, but please note, if your outer CFIF evaluates false, you will have that AND keyword just hanging there...

;-)

rudy

http://r937.com/

SQL Consulting

 

[webmigit]

Good point, can't we chalk that one up to it being 2:30 in the morning, just this once?

I was really tired man... exhausted, just couldn't sleep.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[webmigit]

Must be tired today, forgot my webmigit password last night and am still logged in here today so yay so figured what the heck... anyway.. yeah I'm alltheverses too... oh well might come in handy anyway... using it for a seperate company kinda filtering out emails for posts for projects on either site.

Yay for me, got webmigit back.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[05082000]

Thank You all for your help it worked