excluding query results? 1

[simmerdown]

Here's the basic query:

[COLOR=006699]

<cfquery datasource=&quot;staff_directory&quot; name=&quot;personinfo&quot;>
SELECT PersonID, FirstName, NickName, MiddleName, LastName
FROM Person
 WHERE 0=0
 AND FirstName LIKE '%#Form.Name#%'
  OR LastName LIKE '%#Form.Name#%'
  OR NickName LIKE '%#Form.Name#%'
   ORDER BY LastName
</cfquery>

[/color]

I'm trying to modify the query to exclude any results that have the word 'Modem' as a part of the LastName field. I've tried including WHERE [COLOR=006699]

 NOT LastName LIKE '%Modem%'

[/color] and a few other variations on that theme, but with no success so far. The results that have the word 'Modem' still show up. Any solutions? [sig][/sig]

 

[simmerdown]

OK, an update. It's easy enough to have the results not show up in the page ... just use a few <cfif>s to prevent any unwanted results from showing up. However, I still haven't figured out how to do this in the query itself, which is important in the RecordCount consideration. If I just use <cfif> to hide certain results, RecordCount will still indicate that I did have some matches for my search. I want to tell the user that there were no matches. [sig][/sig]

 

[Lpic]

OK, I know this is pretty inelegant, but why don't you just do a &quot;modem&quot; count in your <cfif> statement and subtract from the recordcount if all you want is to display the number of matches? Or, since I'm kind of a newbie, am I missing something? If nothing else, couldn't you do this at least with <CFLOOP>? [sig][/sig]

 

[perichazhi]

I hope u can modify the query as

SELECT PersonID, FirstName, NickName, MiddleName, LastName
FROM Person
WHERE 0=0
AND Upper(LastName) NOT LIKE 'MODEM'
AND FirstName LIKE '%#Form.Name#%'
OR LastName LIKE '%#Form.Name#%'
OR NickName LIKE '%#Form.Name#%'
ORDER BY LastName

I'm sure this would work
Perichazhi..


[sig][/sig]

 

[simmerdown]

Thanks for the idea, peri ... it didn't work though. I got this message back:

[COLOR=003366]

Undefined function 'Upper' in expression

[/color]

I'd like to know where the 'upper' thing came from ... is it a function that I'd need to, um, define?

_____

Lpic, you're right about the inelegance, but if there's no elegant way to do it, your functional way will suffice. I may be missing something because I'm pretty much a newbie myself, and haven't ever used <cfloop> in a CF app before. I'm going to go read up on it.

_____

Meanwhile, if anyone else wants to try their hand at this, be my guest! [sig][/sig]

 

[bjkersey]

I just tried this on my database and it worked!
SELECT emplid, first_name_srch,last_name_srch
FROM empl_personal_data
WHERE
(last_name_srch NOT LIKE 'ABBOTT')
AND (first_name_srch LIKE '%B%'
OR last_name_srch LIKE '%B%')
ORDER BY Last_Name_srch

I know because the first time I did
SELECT emplid, first_name_srch,last_name_srch
FROM empl_personal_data
WHERE
(last_name_srch NOT LIKE 'MODEM')
AND (first_name_srch LIKE '%B%'
OR last_name_srch LIKE '%B%')
ORDER BY Last_Name_srch
and I saw ABBOTT so I cahanged &quot;MODEM&quot; to &quot;ABBOTT&quot;
ran it again, and no ABBOTT
Hope this helps [sig][/sig]

 

[simmerdown]

I just stumbled onto the same result myself, but I do appreciate the contribution, [COLOR=003366]bjkersey[/color]. I was able to add a &quot;publish: yes/no&quot; field to the database, which helped ... the essential part, though, is those parentheses. Placing the multiple OR portions inside the ( ) is necessary, otherwise the first condition of NOT LIKE is circumvented.

Thanks everyone.
Here's the final code:
[COLOR=003366]

SELECT PersonID, FirstName, NickName, MiddleName, LastName, PubOnWebDirectory
FROM Person
 WHERE PubOnWebDirectory = 1
  AND (FirstName LIKE '%#Form.Name#%'
       OR LastName LIKE '%#Form.Name#%'
       OR NickName LIKE '%#Form.Name#%')
   ORDER BY LastName

[/color] [sig][/sig]