SQL Select with comma separated column value

Report · Jun 23, 2006

Hi All

ASP VBScript

I have a DB column named allowed_contracts that stores a comma separated
list of of values e.g. 3, 5, 19, 44, 52

I need to select records based on a variable called varContractList that
contains another comma separated list i.e. 5, 44, 52

I only want to select records where the allowed_contracts column contains
each of the varContractList,

For example only return records that have 5 or 44 or 52 in thier
allowed_contracts column.

My brain is now in a persistant vegetive state trying to work this out so
any ideas would be much appreciated.

Regards
Bren

Report · Jun 23, 2006

Fatal flaw here Bren. In a relational database, one shouldn't really store
foreign keys as a comma delimted string. One should have a 3 table
structure. eg

Products:
ProductID
Product

Categories:
CategoryID
Category

ProductCategories:
ProductID
CategoryID

So, from the table ProductCategories, a product can belong to many
categories. When doing a front end search to find products in multiple
categories, products can be shown thus:

select * from Products where ProductID in (select ProductID from
ProductCategories where CategoryID in (5,44))

--
Jules
http://www.charon.co.uk/charoncart
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

Report · Jun 24, 2006

Hi Jules

Cheers for this.
I was trying to be a bit cute (lazy even) by storing the project ID's as a
comma delimted string but as we see it doesn't always pay to be lazy. Serves
me right for destroying brain cells by drinking the Welshpool beer the other
week whilst visiting mates down that neck of the woods. :-))

Time for another table me thinks.

Rgds
Bren

"Julian Roberts" <nospam@charon.co.uk> wrote in message
news:e7i13f$mup$1@forums.macromedia.com...
> Fatal flaw here Bren. In a relational database, one shouldn't really store
> foreign keys as a comma delimted string. One should have a 3 table
> structure. eg
>
> Products:
> ProductID
> Product
>
> Categories:
> CategoryID
> Category
>
> ProductCategories:
> ProductID
> CategoryID
>
> So, from the table ProductCategories, a product can belong to many
> categories. When doing a front end search to find products in multiple
> categories, products can be shown thus:
>
> select * from Products where ProductID in (select ProductID from
> ProductCategories where CategoryID in (5,44))
>
> --
> Jules
> http://www.charon.co.uk/charoncart
> Charon Cart 3
> Shopping Cart Extension for Dreamweaver MX/MX 2004
>
>
>
>
>