LIKE operator; problem searching

[minsan]

I'm having trouble using the LIKE operator in one of my cfqueries. Here's some background info. I have two tables, namely, Projects and Employees, like so:
[tt]
PROJECTS Table
index title employee_id
1 Build DB 102
2 Create Graphics 101,102,104
3 Write Code 102,103
4 Test Program 103

EMPLOYEES Table
id first last
101 John Lennon
102 Paul Mccartney
103 George Harrison
104 Richard Starkey
[/tt]
projects.employee_id must be a (comma-delimited) string data type AND
employees.id must be a number data type

Now here's the problem:
I have a search page that lets a user enter an employee_id, and the results page will display all projects that the employee is working on. If 0 is entered, it will output ALL employees. Here's my code:
[tt]
<cfquery name=&quot;results&quot; datasource=&quot;ds&quot; dbtype=&quot;ODBC&quot;>
SELECT projects.index,projects.title,projects.employee_id,employees.id,employees.first,employees.last
FROM projects,employees
WHERE 0 = 0
<cfif form.employee_id EQ 0>
AND projects.employee_id LIKE '%#form.employee_id#%'
</cfif>
AND employees.id LIKE projects.employee_id
ORDER BY employees.first
</cfquery>

<cfoutput query=&quot;results&quot;>
#results.first# is working on #results.title#.<br>
</cfoutput>
[/tt]
When I try to search, I only get records that doesn't contain a comma in the employee_id column. For example, if I search for all employees, I only get Projects 1 and 4; if I search for employee 102, I only get Project 1. I tried the IN operator instead of LIKE, but I got a data type mismatch error. I've also tried wildcards, subqueries and other operators, but still didn't work.

Any suggestion would be helpful. Thanks!

minsan

 

[tleish]

Try using IN instead:

<cfquery name=&quot;results&quot; datasource=&quot;ds&quot; dbtype=&quot;ODBC&quot;>
SELECT projects.index,projects.title,projects.employee_id,employees.id,employees.first,employees.last
FROM projects,employees
WHERE 0 = 0
<cfif form.employee_id EQ 0>
AND projects.employee_id LIKE '%#form.employee_id#%'
</cfif>
AND employees.id IN projects.employee_id
ORDER BY employees.first
</cfquery> - tleish

 

[minsan]

I tried IN instead of LIKE, but I got an error saying cannot convert '101,102,104' to data type int. Unofortunately, because of the way the system is set up, projects.employee_id must be a string data type and employees.id must be an integer data type. So, I just can't change the data types either.

Any other suggestions?

minsan

 

[duns]

Hi,

Why don't you use the ToString function

So your code would be something like

AND ToString(employees.id) IN projects.employee_id.

Might not work, but worth a try

 

[tleish]

Try this:

<CFPARAM NAME=&quot;FORM.employee_id&quot; DEFAULT=&quot;0&quot; TYPE=&quot;numeric&quot;>
<cfquery name=&quot;results&quot; datasource=&quot;ds&quot; dbtype=&quot;ODBC&quot;>
SELECT projects.index,projects.title,projects.employee_id,employees.id,employees.first,employees.last
FROM projects,employees
WHERE 0 = 0
<cfif FORM.employee_id NEQ 0>
AND projects.employee_id LIKE '%#form.employee_id#%'
AND employees.id = #form.employee_id#
</cfif>
ORDER BY employees.first
</cfquery>

<cfoutput query=&quot;results&quot;>
#results.first# is working on #results.index#. #results.title#.[COLOR=000080]<br>[/color]
</cfoutput> - tleish

 

[minsan]

tleish,

I tried your suggestion and here are the results. When I enter an individual employee, it works. I entered 102 and it picked out Projects 1, 2, and 3. But when I entered 0 for ALL, I got 0 results. It didn't error out, but no records were found. I think it might be because of this line:
AND employees.id = #form.employee_id#
This line makes it look for an employee id of 0, which doesn't exist. So I guess half the problem is solved. Anything more I can try?

duns, I also tried the ToString function but it didn't work.

minsan

 

[minsan]

To this forum's experts...

Can somebody at least tell me whether the problem I described above has a possible solution? Maybe I can stop looking for answers if there isn't any.

Thanks.

minsan

 

[tleish]

Do you have it set up like this?

<cfif FORM.employee_id NEQ 0>
AND projects.employee_id LIKE '%#form.employee_id#%'
AND employees.id = #form.employee_id#
</cfif>

If you do, it shouldn't be searching for 0 at all. - tleish

 

[duns]

I don't think the problem is that he is searching for 0, but if he puts in zero then he gets all the results but they are wrong. As in he gets a list of all the employees as assigned to all of the projects.

The best way to do this is to create a lookup table that combines the employee_id from projects and the id from employees. I know this is a hassle but it will save you the pain. Because of the way SQL is there is no easy way to deal with a column that has commas in it.

duns

 

[minsan]

tleish,

I think my reply to your second suggestion was inaccurate. I must have left off the cfparam line or something. Anyway, I tried your suggestion again and it only works if I search for an individual employee. When I search for ALL, I get 16 results. Here's the SQL statement from the debugger:

results (Records=16, Time=8572ms)
SQL =
SELECT projects.*,employees.*
FROM projects,employees
WHERE 0 = 0

ORDER BY employees.first

I guess without a WHERE statement that connects the projects to the employees table, I get compounded results, as in the number of rows in projects times the number of rows in employees.

What do you think?

minsan

 

[minsan]

duns,

You might be on to something. How do I create that lookup table that combines the data from the two tables?

minsan