Skip to main content
D
Dax_Trajero
Inspiring
April 28, 2010
Question

problem with SELECT WHERE IN

  • April 28, 2010
  • 2 replies
  • 2525 views

I'm having a problem with a SELECT clause...

the variables...

<cfset MyString=#getProducts.matches_with#>

<cfset myArrayList = ListToArray(MyString)>

the query...

SELECT *

FROM pricelist

WHERE supplier_code IN ('#myArrayList[1]#' , '#myArrayList[2]#')

The problem is, the select clause is only ever retrieving one row, despite their being several matches

The clause works fine when I change the WHERE line to

WHERE supplier_code IN ('E1775' , 'R1771')

What am I doing wrong here ?

This topic has been closed for replies.

2 replies

D
Dan_Bracuk
Inspiring
April 28, 2010

Your specific problem is most likely with this line:

<cfset MyString=#getProducts.matches_with#>

dump that variable and see if it is what you expect.

If the getproducts query and the next query are from the same database, you can avoid the problem altogether by joining tables in the getproducts query.  If you don't know how, I've heard good things about the book, Teach Yourself SQL in 10 Minutes by Ben Forta.

D
Dax_TrajeroAuthor
Inspiring
April 28, 2010

To answer you point, I've dumped that variable and it contains what it's supposed to eg. E7171, A1211, F3232

Think of a jewellery site, and you're viewing a page about a single piece of jewellery

On that same page, there's three empty boxes at the base in which each should display details of matching jewellery

So the merchandise table contains two important fields:

product_code (each item of jewellery has a unique product code)

matches_with (each item of jewellery has a field containing a comma separated list of product codes that match this item of jewellery

I'm simply trying to grab that matches_with field, turn it into an array and then run a select clause to go and get details on each product in that array

Am I missing a simpler more elegant solution ?

ilssac
ilssac
Inspiring
April 28, 2010

Dax Trajero wrote:

Am I missing a simpler more elegant solution ?

WHERE supplier_code IN (<cfqueryparam value="#getProducts.matches_with#" list="yes" cfsqltype="CF_SQL_VARCHAR">)

This does assume that getProducts.matches_with contains the entire list you expect it to.

P.S.  The advice to peruse a resource such as "Teach Yourself SQL in 10 minutes" will help explain why having a field in a database that contains a comma delimited list of foreign key values is a bad database design and how to properly do it with a relational table.

ilssac
ilssac
Inspiring
April 28, 2010

Have you output the variables myString and|or myArrayList to confirm that they contain the values you think they do?

You probably want to look at some other CFML functionality that could simplify what you are trying to do.

I.E.

valueList() and listQualify() or the ultimate, <cfqueryparam value="..." list="yes"...>

R
rab890
Known Participant
April 28, 2010

Greetings,

I will be out of the office on Thursday April 29th and Friday April 30th, returning on Monday May 3rd. I will return all emails when I return. If you require immediate assistance, please contact Rob Panico at (518) 827-8282, rob_panico@altsystem.com.

Thanks,

Ron Barth

Alternative Systems

www.altsystem.com

603.537.9473

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices