Query a comma delimited list

You should avoid having a list value in a field and normalise your table. But if you want to stick with your style(which is not advisable), maybe you can do this. I believe your supplier id is a string so the code below may cause slowness in your system:

<CFQUERY NAME="GetAuctions1" DATASOURCE="#Application.Datasource#">
SELECT Suppliers FROM Auctions
</CFQUERY>

<cfoutput query="GetAuctions1">

<CFQUERY NAME="GetAuctions2" DATASOURCE="#Application.Datasource#">
SELECT * FROM Auctions
WHERE '#Session.SupplierID#' IN(<cfqueryparam values="#Suppliers#" cfsqltype="CF_SQL_VARCHAR" list="Yes">)
</CFQUERY>

</cfoutput>



But if your supplier id is a numeric value. then you can do this:

<CFQUERY NAME="GetAuctions" DATASOURCE="#Application.Datasource#">
SELECT A1.* FROM Auctions A1
WHERE #Session.SupplierID# IN(SELECT A2.Suppliers FROM Auctions A2 WHERE A2.your_primary_key_for_table_Auctions = A1.your_primary_key_for_table_Auctions)
</CFQUERY>

The only way to do this query correctly is to have 3 LIKE string checks. One where the matching ID is at the beginning of the list, one where it's at the end, and one where it's contained somewhere in the middle.

The SQL IN operator doesn't support how you're trying to use it, nor was it designed to.

<CFQUERY NAME="GetAuctions" DATASOURCE="#Application.Datasource#">
SELECT * FROM Auctions
WHERE Auctions.Suppliers LIKE '#Session.SupplierID#,%' OR
Auctions.Suppliers LIKE '%,#Session.SupplierID#,%' OR
Auctions.Suppliers LIKE '%,#Session.SupplierID#'
</CFQUERY>

That said, I recommend you fix your data model. If you have a comma-delimited that you need to search or reference by individual item in that list, your data model is broken and needs to be re-evaluated.

quote:

---

Originally posted by: harpso
Suppliers is a field containing a comma delimited list of Supplier ID's.
When a supplier logs in they should be able to view all the auctions that they have been registered for
i.e if their supplierID is in the suppliers field.

have tried this and get an error:

<CFQUERY NAME="GetAuctions" DATASOURCE="#Application.Datasource#">
SELECT * FROM Auctions
WHERE '#Session.SupplierID#' IN 'Auctions.Suppliers'
</CFQUERY>

have tried this and recordcount is 0 when it should be 3:

<CFQUERY NAME="GetAuctions" DATASOURCE="#Application.Datasource#">
SELECT * FROM Auctions
WHERE '#Session.SupplierID#' LIKE 'Auctions.Suppliers'
</CFQUERY>

---

Your 1st query crashed because you have to surround your list with brackets when you use the sql keyword "in". But your biggest problem is that you are storing lists in a field. Normalize your database and it will work better for you.

Try something like this:

<CFQUERY NAME="GetAuctions" DATASOURCE="#Application.Datasource#">
SELECT * FROM Auctions
WHERE Auctions.Suppliers LIKE ''%#Session.SupplierID#%''
</CFQUERY>

Auctions.Suppliers must appear without quotes, or else is treated as string