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 flexcoders@yahoogroups.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? >
