yeah, normalizing it would make it straight forward and the left outer would then work. barring that then read the data into CF, and loop the list. or use the find function to find the category for the businesses for Q 2 Rob
On Sat, Nov 15, 2008 at 12:29 AM, Zaphod Beeblebrox < [EMAIL PROTECTED]> wrote: > sql doesn't deal well with lists in columns. You will probably have > to read that in with a cfquery, parse the list inside the column and > then loop through it using more cfquery tags. > > Any chance you could normalize that data? > > On Nov 14, 2008, at 11:22 PM, Erika L. Walker wrote: > > > A hopefully simple problem for some here ... > > > > I have a table full of categories, assigned ID #'s. > > > > I store those category ID #'s in a comma delimited list in a field > > in a row > > of data for a business. A business can have more than one category. > > > > #1. I need to display a list of those businesses, along with the > > categories > > they are in. > > > > AS WELL AS > > > > #2. If I do a search by category, then I need to display a list of the > > businesses that have that category assigned to them. > > > > I was thinking it has something to do with "IN" but my query wasn't > > working > > ..... > > > > > > I *think* I'm also looking for a left outer join ... > > > > Select m.member, c.category > > from m.members LEFT OUTER JOIN c.categories > > where c.ID IN m.categories > > > > or something to that effect .... > > > > > > Help! :) > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-community/message.cfm/messageid:280460 Subscription: http://www.houseoffusion.com/groups/cf-community/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.5
