Querying where item in a list

[tyrant1969]

In my DB I have a field which contains one of three things - blank(nothing), a single item, or a list of items that are comma separated.

For example the first two entries contain

print
stat,print


I am running a query to grab the items that contain print as either the only item in the list or one of the items.

Here's the query code:

  <cfquery datasource="#application.ds#" name="getlist">
    select itemname,reqs from items
	where 'print' in (reqs)
  </cfquery>

It returns one record, the one where the field only reads as "print". I've even tried putting the where clause to read with ',print' and then get absolutely nothing (of course).

Any thoughts?

 

[GUJUm0deL]

Change your query to this and test:

  <cfquery datasource="#application.ds#" name="getlist">
    select  itemname,reqs 
    from    items
    where   reqs LIKE '%print%'
  </cfquery>

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[GUJUm0deL]

For my test I got back 'print' when it was single word or inside a comma list.

The percent-sign will retrieve ANYTHING that is either enclosed between it or after or before it.

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[tyrant1969]

That will work and since I will control the data that goes into the reqs field won't cause any problems, but if I ever added say "printer" or something, then it would cause an issue.

But for now, I'm going to go with the obvious answer, it was so simple I have no idea why I didn't think of it in the first place! D'OH!

Thanks again!

 

[GUJUm0deL]

tyrant1969, no probss. What can you do is if you're passing the value via a form then you can change the hardcoded 'print' to '%#FORM.FieldName#%' or '%#URL.URLName#%' and so forth...

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[CTekMedia]

If you need to avoid the print printer issue - run your query without qualifications, e.g.,

<cfquery datasource="#application.ds#" name="getlist">
select itemname,reqs
</cfquery>

Then:

<CFLOOP QUERY="getlist">
<CFIF ListContainsNoCase(getlist.reqs, "print")>

Cheers,

Bluetone

 

[r937]

bluetone, what if there are 1,000,000 rows, and there are 999,000 rows for 'printer', you're looking for 'print'

you're going to retrieve them all and do the search in cf?

no, it's always best to do this in the database

select itemname,reqs from items
  where ','||reqs||',' like '%,print,%'

r937.com | rudy.ca