challenging problem -spliting different values within one field
Posted by:
Ali Hesean
Date: April 01, 2014 08:07PM
Hi everyone,
I'm new to sql and i have a problem that i couldn't solve by my own. I hope some one could help. Or gives me a hint if possible, honestly i would like to do it my self as much as possible!
These are two tables:
catalog table:
sku, product_name, categories
LG123456789, LG TV 40, |15|10|14|
AL123456789, iPod, |12|11|10|
CL123456789, Blue Ballerina, |1|6|8|
RL123456789, Belt, |2|1|5|
and categories table:
id_category, category_name, depth
1, Fashion, 0
2, Men's Fashion, 1
3, Men's Clothes, 2
4, Men's Shoes, 2
5, Men's Accessories, 2
6, Women's Fashion, 1
7, Women's Clothes 2
8 Women's Shoes, 2
9, Women's Accessories, 2
10, Electronics, 0
11, Audio, 1
12, Audio Accessories, 2
13, MP3 Players, 2
14, TV & Home Theaters, 1
15, TV, 2
16, Home Theaters, 2
So what is required is: split the data in column `categories` from table 1 catalog. The column `categories` points to the `id_category` in table 2`categories`. And then display all corresponding rows that match the the three ids in the column using data from table 2(`categories`) listed by depth. Note how values in column categories are separated by || which makes things even more complicated
Edited 2 time(s). Last edit at 04/01/2014 08:11PM by Ali Hesean.