Using MSSQL to check a list

[Fish Barrel]

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.

[ilssac]

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

[Fish Barrel]

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]

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.

[Fish Barrel]

in my attribute table on MS Access I have two fields (att_id,attribute_values).

The att_id is the index number and the attribute_values contains sizes seperated by commas.

IE:

att_id(field name)_____________attribute_values (field name)

1__________________________1,2,3,4,5

2__________________________2,3,4,5

3__________________________5,6,7,8

In my products table on MS Access I have a field (att1)

These values refer to the att_id in my attributes table.

IE:

att1 (field name)

2

1

3

I need to able to use the products.att1 values to refer to the attributes.att_id index numbers. Once it finds the matching index number (att_id), I will need to compare my #form.searchsize# to the values within the list of attributes.attribute_values...

I do have a list of sizes seperated by commas and that is for a particular reason. Is there any way this can be accomplished. If it is too much trouble I will keep looking. Thanks for your help.

[ilssac]

Ideally you would not have a list of values in the attribute_values field.

I can't say if this should be in the attributes table or another table that describes sizes.  But you want some table or the other that has records something like this.

att_id	size
1	1
1	2
1	3
1	4
1	5
2	2
2	3
2	4
2	5
3	5
3	6
3	7
3	8

You could then write a simple SQL statement like this

SELECT att_id

FROM att_size_table

WHERE size = 5

To get other related information you could write stuff like this

SELECT

   aField,

   bField,

   cField

FROM

   products INNER JOIN

   attribute ON (products.att_id = attribute.att_id) INNER JOIN

   att_size_table ON (attribute.att_id = att_size_table.att_id)

WHERE

   size = 5

Otherwise with your list in that field you are going to have to use stuff that looks like my origianal post.

WHERE

   attribute_values = '5' OR

   attribute_values LIKE '5,%' OR

   attribute_values LIKE '%,5' OR

   attribute_values LIKE '%,5,%'

And just guess what happens to that logic if you start having sizes like 15,25,55 etc.

[Fish Barrel]

Well my friend, I am pretty sure you have come up with the most efficient way to do this. I am going to have to make some changes the the database, but that is what needs to be done. Thank you for your help and I am glad there are people out there like you. Could not learn with out your help!