Thanks Jeff. I already started that process. I was hoping that since I
have so many relationships I would have to do that...just being lazy.
On 2/20/2010 8:00 PM, Jeff wrote:
I strongly recommend that you modify your database structure so that
you do not store comma separated lists in a field.
Read up on many to many relationships and intersection/linking tables.
If I had to theorize on your problem; SQL is seeing your 'ageGroups'
and 'accountType' fields as a single value; not as a list.
You might be able to do some string processing magic, but it sounds
like a nightmare.
--- In [email protected]
<mailto:flexcoders%40yahoogroups.com>, Wally Kolcz <wko...@...> wrote:
>
> I know this is out of the core topic, but I am banging my head on this
> simple issue..or probably is...
>
> I am trying to write a SQL query (in ColdFusion) for my database (MySQL
> 5) when I am looking for a passed in argument value in the column
(which
> is set as a varchar).
>
> I am passing in '0' for the ageGroup and 'kids' for the accountType I
> have 'kids'
>
> My Query is:
>
> Select CONCAT(roomDisplay," (National)") as roomDisplay,
> CONCAT(<cfqueryparam value="#arguments.ageGroup#">,
> "-", roomDisplay, "-", "National") as roomID
> FROM chatrooms
> WHERE <cfqueryparam value="#arguments.accountType#"> IN
> (accountType)
> AND <cfqueryparam value="#arguments.ageGroup#"
> cfsqltype="cf_sql_integer" /> IN (ageGroups)
> ORDER BY roomDisplay ASC
>
> I have a record where 'ageGroups' is '0, 1, 2, 3, 4, 5, 6, 7, 8' and
the
> 'accountType' is 'kids, siblings, parents'
>
> I keep getting 0 returns but I know both values exist in at least 1
record
>
> What am I doing wrong?
>
