Tough SQL Query - Comma delimited fields

First of all, I take no responsibility for the way the database was originally designed. Anyhow, here it is.

MasterProducts
-----------------------------------
sku | id | title | etc.


Site #1 Products
-----------------------------------
id | title | outputName | etc.


Categories
-----------------------------------
id | title | outputName | products


So, we have a MasterProducts table that has all of our products with the product SKU as the primary key. This table also has the product id field. We have multiple sites, and some of our products are available on each site (so, Site #1 Products), and on each of these sites the product may have a unique title, outputname, etc. The id in the site specific products is the same as the id in the master products table for the sku. More accurately, the site specific ID is a foreign key in the masterProducts table.

The categories table has a products field where all the products in that category are organized by ID in comma delimited format. Don't blame me for this...whoever designed the software/database in the beginning did it this way...*sigh*. Anyway, so it looks like:

1 | Books | books.html | 1234,3123,1245,1231,532,1244,3535,3521,etc.
2 | Biographies | bios.html | 1234,4124,1242,etc.


What I need to do, that is the difficult part, is get a list of all the SKU's in the MasterProducts, and for each record get a list of what categories these products are in. So, product with SKU ABC and ID 1234 would return:

ABC | Books, Biographies

I would also settle for:

ABC | Books
ABC | Biographies

I'm just trying my best to figure this one out and it's not working. We need it to locate any products in our database that haven't been added to any category.

Please offer your wisdom, oh Experts of SQL!

Thanks. :)

Matt


LVL 2
mbeckmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gad_flyCommented:
hi

i think we must redraw the category table in another table with some query  if all the product in one column with ' , ' seperated .

(i get it right ? because i can't find any logic , behind of they did.)

Let me know if it's true.

Mohammad Pourebtehaj
0
StephenCairnsCommented:
something like

select title,outputName from Categories
where CHARINDEX(to_char(1234), products)>0

0
gad_flyCommented:
Hi StephenCarins that's good point :)

i want to say this but i'm not sure about what  mbeckman problem . :)))

and you did it , we can make it with some param for 1234 .

thanks StephenCairns  :)

Mohammad Pourebtehaj
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Anthony PerkinsCommented:
A slight modification to the query posted by StephenCairns (no points please):

select title,outputName from Categories
where CHARINDEX(CAST(1234 as varchar(20)), products)>0
0
mbeckmanAuthor Commented:
gad fly:

Yes...I'm pretty sure that's the best way to do it. Somehow put all the category data in a new temporary table such as:

CATNAME | 1234,4412
CAT2 | 4123,1234,4412

to -->

CATNAME | 1234 <-- product ID
CATNAME | 4412
CAT2 | 4123
CAT2 | 1234
CAT2 | 4412

...and THEN I need to somehow go through this temporary table, and map the product ID to the product SKU, and then return a result set for each SKU that has a list of the category output names.

Does that make more sense?

Thanks.
0
gad_flyCommented:
Hi mbeckman

I agreed with you .

let see what we want .

  for first step create SP for create this table with your existing data ,
 and then create Trigger for update your actual table to check the last parameter that insert to this category and put it in our temp table .

 ( why temp table ? we can create the Category table and CategoryProduct table and use it . right ? )


if you ok with that let me now to begin it together :)

Mohammad Pourebtehaj
0
mbeckmanAuthor Commented:
Yes, we need to first make an SP that can create the table with the data below.

CAT1 | 1234
CAT1 | 3124
CAT1 | 4124
CAT1 | 1234
CAT2 | 1234
CAT2 | 4125
CAT2 | 8124

What I have right now is a function that takes a list of items and returns a table of those items, which I think I need in order to do this. It is used like:

SELECT *
FROM ufSplitString('1234,4124,1231,1255,5555',',')

It returns:

Tokens
--------
1234
4124
1231
1255
5555

So, is this what I need in order to create the table?

Thanks.
0
gad_flyCommented:
Hi again

maybe we can write in  this logic way for SP ( i just thinking )

use your function to count all the delimiter item in one title and then use this for create a loop to insert rows with same title and 1st,2nd,.... item .
( we must put the delimiters item in array  for every row we read to call them for insert part.)

Does that make more sense?

Mohammad Pourebtehaj

0
mbeckmanAuthor Commented:
gad_fly:

Thanks for trying to help. However, I decided the best method for what I needed to do was just use a piece of C# code to create the relation table as I needed it, and then loop through each SKU, querying the database for related categories. Not a dedicated SQL solution, but because this would only be performed quarterly, it's not that big of a deal.

Thanks,

Matt
0
gad_flyCommented:
hi Matt

i enjoyed with this Q :)

i am not ok with your last idea ( "Not a dedicated SQL solution")  but it's your choice :)

i already going and try to solve it anyway , i think this Q is a  good practice enviroment .

Thanks For Q :))

Mohammad Pourebtehaj
0
GranModCommented:
PAQed with points refunded (250)

GranMod
Community Support Moderator
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.