-1

I have emp records records that look like this:

name, id, gender, itemvalue
steve, 123, M,    (3,4,5)
bond, 456,  M,    (5,4)
James, 345,   F,  (4,7)

In another table I have references of the itemvalues like this:

3='test' 
4='coder'
5='admin'

Now in the record value, How do I check a single value from itemvalues? For example, check whether Steve's itemvalue is 5 or not in ColdFusion?

<cfif steve.itemvalue EQ 5>
      do this
<cfelse>
      if not 5 do this
</cfif>
Leigh
  • 28,765
  • 10
  • 55
  • 103
TGR
  • 107
  • 1
  • 3
  • 11
  • 2
    If you mean you are storing a *list* of values in a single database column, do yourself a favor - don't. Storing lists is just asking for problems (convoluted sql, poor query performance, data integrity issues, etcetera). Instead, you should [normalize your tables](http://stackoverflow.com/questions/9944754/storing-ids-as-comma-separated-values/9946106#9946106). Create a third table that stores the related emp + item values. – Leigh Jul 23 '15 at 22:51
  • Also, which version of CF are you using: 9, 10 or 11? What have you tried? – Leigh Jul 23 '15 at 22:57

1 Answers1

0

You can use ListFind() like this:

<cfloop query="getEmployee">
  <cfif findNoCase("steve", getEmployee.name) AND listFind(getEmployee.itemValue, "5">
    do this
  <cfelse>  
    if not 5 do this
  </cfif>
</cfloop>
Abhishekh Gupta
  • 6,206
  • 4
  • 18
  • 46
  • 1
    Looking at the OP, "name" does not contain a list of values, only the "itemValue" column. Nothing against the answer, but as mentioned in the comments, storing "lists" in a db column is a very bad idea. It usually creates more problems than it solves. – Leigh Jul 24 '15 at 16:08
  • @Leigh Yes, I agree with you. But, I added this answer as the query results after using mapping table would be the same as of `itemValue` if grouped. – Abhishekh Gupta Jul 26 '15 at 11:14
  • Not unless they specifically formatted them into some sort of list using SQL functions. A basic JOIN with a junction table would return individual values. @TGR - One of the reasons lists are discouraged is because db's are optimized to work with sets (or rows) of data, NOT delimited strings. So using a junction table offers a lot more flexibility than lists. For example, how would you write a query to a) Find employees having both item 4 and 5? or b) Find employess that have items 4 and 7 - but NOT item 1? With a junction table, it is a simple JOIN. Using lists, not so much ... – Leigh Jul 26 '15 at 17:18