I have a cfquery pulling records from a database, one of the columns returned contains a comma seperated list of integers. So the returned query would look something like this:
I then need to filter that query using coldfusion's query of query feature based on a colour. So say I wanted all the products with colour 5 it would return products 1, 3 and 4. This would be easy if I could use the MySQL function FIND_IN_SET but because its coldfusion's query of query feature it doesn't support FIND_IN_SET
Attached is an example of the first database query and what I've tried for the query of query. Supllying a URL.colourid value of 5 would return product 4 but not 1 and 2. My actual code is a lot more complex than this, but the basic premise is the same.
I've looked for hours trying to find an example that does this but haven't had any luck.
Any help on how to do this would be great, as it's driving me mad!
<cfquery name="getProductsQuery" datasource="#APPLICATION.datasource#">
<cfquery name="products" dbtype="query">
WHERE '#URL.colourid#' IN (colour_ids)