Help with a find and replace query.

[vbvamsi]

Hi, I have two tables CategoryMaster and ProductMaster.

category_id Category Name
10 Test1
11 Test2
15 Test3
28 Test4

products_id model categories
301 modA 10|11
302 modB 11|15
303 modC 10|28|15

I want to display the category values along with the products. Can someone help me with a query or a function which lets me do this. The end result should be as below.

products_id model categories
301 modA Test1|Test2
302 modB Test2|Test3
303 modC Test1|Test3|Test2

I need the category numbers in the product replaced by the respective category name and "|" is the delimiter seperating the categories.

 

[jdraw]

You should normalize your table. You have a repeating field Category(ies) in your ProductMaster.
I don't know your application but I suggest 3 tables (based on my limited understanding of your app)

CategoryMaster
ProductMaster and a junction table ProdCategory that has 2 fields

ProductsId
CategoryId

and I would create a compound primary key on this combination of fields

Your data in the new table would be
301 modA 10
301 modA 11
302 modB 11
302 modB 15
303 modC 10
303 modC 15
303 modC 28

You will need a query that relates these 3 tables.

I am attaching a jpg of a query design(similar to your set up I think) using 3 tables
(Books,
Authors,
BookAuthors)

from one of my databases showing how the tables relate.

Here is the accompanying SQL

SELECT BOOKAuthors.BookID
, AUTHOR.authorId
, BOOK.BookTitle
, BOOK.NumPages
, AUTHOR.Firstname
, AUTHOR.Lastname
FROM AUTHOR INNER JOIN (BOOK INNER JOIN BOOKAuthors ON
 BOOK.BookId=BOOKAuthors.BookID) ON
 AUTHOR.authorId=BOOKAuthors.AuthorId
ORDER BY BOOK.BookTitle, AUTHOR.Lastname;

Hope this is helpful.

You should research Normalization.
see first topic here
http://www.rogersaccesslibrary.com/forum/topic238.html

 

[vbvamsi]

hi jdraw, I don't want to make it complicated. I have limited sql knowledge and I have written a few queries already to give me my end output. If I normalize all the tables, I will have to redo all the queries I already wrote.
I was thinking there will be a simple way of splitting the string, storing in an array and mapping it to categories. If this can be achieved, it will be very helpful. Otherwise I will have to stick with the current preprocessing I am doing before copying the files to access.