Skip to main content
N
Newsgroup_User
Inspiring
June 23, 2006
Question

SQL Select with comma separated column value

  • June 23, 2006
  • 2 replies
  • 245 views
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



This topic has been closed for replies.

2 replies

N
Newsgroup_UserAuthor
Inspiring
June 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
>
>
>
>
>


N
Newsgroup_UserAuthor
Inspiring
June 24, 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





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