Skip to main content
F
Fish Barrel
Known Participant
June 12, 2009
Question

Using MSSQL to check a list

  • June 12, 2009
  • 1 reply
  • 1553 views

SELECT *
FROM attributes
WHERE '#form.searchsize#' IN('attribute_values')


Something like this?

I tried this and could not get it to work.

Basically I need to know if the #form.searchsize# variable is within the data (separated by commas, therefore commas are the delimiter) in each row(record) within the attribute_values field.

attribute_values (MS Access Field)

1,2,3,4,5
1,2,3,4,5,6,7
2,3,4,5,6,7
5,6,7,8,9

I need to check each row's attribute values and see if my #form.searchsize# variable is in it's list.

This topic has been closed for replies.

1 reply

ilssac
ilssac
Inspiring
June 12, 2009

WHERE

     attribute_values = '#form.searchsize#' OR

     attribute_values LIKE '#form.searchsize#,%' OR

     attribute_values LIKE '%,#form.searchsize#' OR

     attribute_values LIKE '%,#form.searcchsize#,%'

And goodness help you have you ever have two digit values in that list.

Of course if you had a properly designed and normalized database disign.  You would then have a table that listed the sizes and a table that listed the attributes and a table that joines the sizes to the attributes.  I'm assuming a many-to-many relationship here.

Then you would be able to write this simple and clean SQL

SELECT *

FROM attributes INNER JOIN attributes-sizes ON (attributes.key = attributes-sizes.attributesKey)

WHERE attributes-sizes.sizeKey = #form.searchsize#

But to each their own.

HTH

Ian

F
Fish BarrelAuthor
Known Participant
June 12, 2009

I do have it set up like that actually. Didnt realize you could do that...

<cfquery datasource="#datasource#" name="searchNarrow_prods">
SELECT *
FROM products INNER JOIN attributes ON (products.att1 = attributes.att_id)
WHERE attributes.attribute_values = #form.searchsize#
</cfquery>

products.att1 is the reference number for the index id number in the attributes table.

attributes.att_id is the id (index number) of each row in my attributes table.

I was not successful with the code you provided me, then again I am a newbie to this.

So basically I need to check the products.att1 and then have my code goto the attributes.att_id and find what id matches with the other. Once I have found a match I need to goto the matching row in my attributes table and access the attribute_values field which contains the size list. Then I can can see if my #form.searchsize# is within that list.

Sorry if I am confusing you... Only way I know how to explain.

ilssac
ilssac
Inspiring
June 12, 2009

I have often heard good things about the books "Teach Yourself SQL in 10 minutes" and "Database Design for Mere Mortals".

Do you or do you not have a field that has a list of values in it?

Your reply start of like you may have a more normalized database design where it would just be a matter of writting the propery join syntax to get the desired results.

But you end with :

Then I can can see if my #form.searchsize# is within that list.

If you can help it at all you do not want to be working with a list, it is very difficult and time consuming to do so.

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