Problem with a query

[hevok]

Hi,

I am having a bit of trouble with a query.
See the code below. There are at least two records that match one or both words in the list.
What am i doing wrong?

[insuractive]

I'm not familiar with the CFQueryParam List attribute, but you might try qualifying the list using ListQualify(list, "'") as I'm pretty sure the SQL syntax requires all strings to be surrounded by apostrophes.

That way you get
jobTitle IN ('Care','Manager')
instead of
jobTitle IN ('Care,Manager')

When in doubt, output your SQL statement as text and see what comes out.

<cfset list = "Care,Manager">
<cfquery name="qGetInfo" datasource="job">
SELECT *
FROM tblJob1
WHERE 0 = 0
AND jobTitle IN (<cfqueryparam value="#ListQualify(list, "'")#" cfsqltype="cf_sql_varchar" list="yes"/>)
</cfquery>

Hope that helps.

[MikerRoo]

No, your syntax is correct and you do not use ListQualify in this case.

Something weird is going on, or you have spaces in the table data, or your table is using a case-sensitive collation.

First, what DB are you using?

Next, run this code and attach the unedited results here:

<cfquery name="qGetInfo" datasource="job" result="zQ_Stat">
SELECT DISTINCT jobTitle
FROM tblJob1
</cfquery>

<CFDUMP var="#zQ_Stat#">
<CFDUMP var="#qGetInfo#">

[hevok]

DB: I use MsSQL 2k

Here are the results of the cfdump:
struct
CACHED false
COLUMNLIST JOBTITLE
EXECUTIONTIME 0
RECORDCOUNT 42
SQL SELECT DISTINCT jobTitle FROM tblJob1
query - Rows: 42
JOBTITLE
1 Another Test
2 Assessment Officer - Special Needs
3 Barking Park HLF Project Manager
4 Business Development Manager
5 Care Manager
6 CCTV Monitoring Officer
7 Childminding Development Officer
8 Communications Careline Officer
9 Community Educational Psychology Service (1 Perm 1 Temp)
10 Day Centre Officer (Level One)
11 Deputy Head of Children's Centre x5 (3 year fixed term contract)
12 Document Imaging and Filing Clerk x2
13 Fitness Instructors - Various Hours
14 Group Manager - Business Transformation
15 Group Manager - Community Housing
16 Group Manager - Housing Services (3 year contract)
17 Group Manager - Systems Development
18 Group Manager- Drug and Alcohol Action Team
19 Group Manager Finance - Resources and Technical
20 Investigations Officer
21 Junior IT Support Analyst
22 Leisure Attendant (Casual)
23 Leisure Attendant (Part time)
24 Lunchtime Support Worker
25 Operations Manager-SENART
26 Park Development Officer (2 year Fixed Term Contract)
27 Play Projects and Development Officer (2 Year Fixed Term Contract)
28 Receptionist/Cashier - Part-Time
29 Receptionist/Cashiers - Casual (various hours)
30 Senior Cashier X2
31 Senior Project Engineer x2
32 Substance Misuse Care Managers x 5 (Social Worker qualified desirable or a relevant other)
33 test
34 Test final
35 Test from web team
36 Test HR
37 Test my final
38 test my test
39 Test upload
40 This is job 1
41 Workplace Skills for Life Tutor
42 Youth Worker - Buildings

[Newsgroup_User]

Right.

And there are no job titles that are either "care" or "manager".

where x in (y1,y2,y3)

Requires an EXACT match between x and one of the items in the list

"care manager" <> "care"; "Group Manager - Business Transformation" <>
"manager".

--
Adam

[hevok]

I see where I was going wrong.

the 'IN' requires an exact match.

IS there a way of using LIKE instead?

Basically, I want users to search for a job where the keywords would either be Care or Manager - and would return both 'Care Manager' and 'Group Manger'.

[hevok]

Actually that is not what I meant. It is a freeform entry - 'Care' 'Manager' are just examples.
Users can type whatever they like, but I want to search for:
'Care' AND 'Manager'
'Care' OR 'Manager'
'Care Manger'.

[insuractive]

What about
<cfset list = "Care,Manager">
<cfquery name="qGetInfo" datasource="job">
SELECT *
FROM tblJob1
WHERE 0 = 0
AND (
<cfloop from="1" to="#ListLen(list)#" index="i">
<cfif i neq 1> OR </cfif> jobTitle LIKE '%#ListGetAt(list,i)#%'
</cfloop>
)

[Dan_Bracuk]

Storing keywords in a separate table would be more efficient.

[MikerRoo]

Insuractive's approach will work but will be extremely slow and inefficient (because the DB will have to do multiple full table scans!).

You do not need to futz around with a keyword table either.

Since you are using MS SQL, you can make good use of SQL's full-text search.

Look up sp_fulltext_catalog, in MS SQL "Books Online", to start.

Post another thread if you want help with that.

[insuractive]

Wow, I wasn't familiar with the SQL server full-text search functionality. That's pretty powerful stuff. Thanks for bringing that up, MikerRoo.

[ArmchairDeity]

I know I may be a bit late with this, but you might try:

<cfset list = "Care,Manager">

<cfqueryparam value="#listQualify(list,'%')#" list="true" ... />

But (someone may have already said this, I don't have time to read the thread in heavy detail) you need to keep in mind that the way you have this query written jobTitle HAS TO BE Care, or Manager, and I'm not sure what the results of adding the wildcard to the IN clause would be, I'm not sure I've ever tried that.

You have the option of querying for any data you want and using a Verity collection to store the query data in a Collection for free-text searchign as well.