How to read string of 0s and 1s 1

[RosieGp]

I have a table of kids(5) and which of the 6 fruits they bring:
Table: KidsData
ID Name FruitData
1 Candy 100000
2 Andy 001000
3 Sarah 001010
4 Gabby NULL
5 Jack 000110
Table: Fruitdescription
ID FruitName
1 apple
2 orange
3 kiwi
4 banana
5 pineapple
6 grape

<cfquery name="kidsData">
SELECT *
FROM KidsData
WHERE FruitData IS NOT NULL AND ID = #ID#
</cfquery>

<cfoutput query="kidsData">
<cfif #left(#FruitData#, 1)# EQ '1'>
Apple
</cfif>
<cfif #left(#FruitData#, 2)# EQ '1'>
Orange
</cfif>
<cfif #left(#FruitData#, 3)# EQ '1'>
Kiwi
</cfif>
<cfif #left(#FruitData#, 4)# EQ '1'>
Banana
</cfif>
<cfif #left(#FruitData#, 5)# EQ '1'>
Pineapple
</cfif>
<cfif #left(#FruitData#, 6)# EQ '1'>
Grape
</cfif>: #Name#<br />
</cfoutput>

The code works fine if there is only 1 fruit in #FruitData# string, like
Apple: Candy
Kiwi: Andy

But for the #FruitData# string that has multiple 1s only shows the first record that it hits for 1, like
Kiwi: Sarah
Banana: Jack

it is not showing the output as needed:
Kiwi: Sarah
Pineapple: Sarah
Banana: Jack
Pineapple: Jack

I don't know where I'm wrong. Any help is appreciated.

 

[LyndonOHRC]

You need to study the Mid() function.
<cfset fruitList=''>
<cfif Mid(kidsData.FruidData,1,1) eq '1'>
<cfset fruitList=ListAppend(fruitList,'apple')>
</cfif>
<cfif Mid(kidsData.FruidData,2,1) eq '1'>
<cfset fruitList=ListAppend(fruitList,'orange')>
</cfif>
...
<cfdump var=#fruitList# />

Lyndon

 

[RosieGp]

Thanks Lyndon. that did help me. I have one more question, might be incorrect for CF thread since it is SQL query question. How can I Join on KidsData and Fruitdescription table to get Fruit Name from Fruitdescription table rather than hard coding it.
this is what I can get to this far.
<cfquery name="kidsData">
SELECT *
FROM KidsData K
LEFT JOIN Fruitdescription F ON F.FruitData = K.ID
WHERE FruitData IS NOT NULL AND ID = #ID#
</cfquery>

Table: KidsData
ID Name FruitData
1 Candy 100000
2 Andy 001000
3 Sarah 001010
4 Gabby NULL
5 Jack 000110
Table: Fruitdescription
ID FruitName
0 apple
1 orange
2 kiwi
3 banana
4 pineapple
5 grape

 

[LyndonOHRC]

You should go to a SQL forum because:
A. The solution depends on the database you are using.
B. This dataset needs to be normalized.
SQL Forum List

Lyndon