LOL, I have to disagree....while I don't think it is good design per say there may be a valid database theory behind it.... It does not break any rules at all if there is good reason to do it.
-----Original Message----- From: Adam Haskell [mailto:[EMAIL PROTECTED] Sent: 25 February 2005 14:27 To: CF-Talk Subject: Re: sql question This is going to get off topic butany time you want to find an idividual value in this comma seperated list your design begins to enter the bad design area. And it does break normailization rules... Adam H On Fri, 25 Feb 2005 14:00:10 -0000, Robertson-Ravo, Neil (RX) <[EMAIL PROTECTED]> wrote: > Yes, strong comma separated lists in a DB is not ideal - though it is not > bad design as in some cases it does make sense - and it does not break any > 'rules'. > > -----Original Message----- > From: Adam Haskell [mailto:[EMAIL PROTECTED] > Sent: 25 February 2005 14:01 > To: CF-Talk > Subject: Re: sql question > > First off i want to point out that anytime you do something like it > should occur to you that this is probably not the best DB design and > could lead to more issues down the road...if this is indeed on of the > rare cases where this design makes sense. Then one option is to do > this CHARINDEX(','+item+',',search_item,0) > 0 > where search item would be something like ',11,' > You could also do a simular thing with LIKE.... ','+item+',' LIKE '%,11,%' > > Adam H > > On Fri, 25 Feb 2005 08:41:20 -0400, Protoculture <[EMAIL PROTECTED]> wrote: > > I have a list of items in a db field [items]... > > > > 1,11,14,12,13,17,15,18,19,16,4 > > > > Now I want to test that my item is one of those numbers thats is in that > field and select that row. See below. If I have one number (11), I want it > to select that row, so it needs to search the string of numbers in the field > [items], unless there is a way to use IN keyword in this case? any ideas? > > > > eq.. > > > > select * from myTable > > where items = 11 > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:196517 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
