Joining computed column with its uncomputed version to show another column with a name

Question

I'm not sure exactly how to say it, let me try with a simple example. I have 3 tables with following columns:

table1
ID_TRANSACTION
ID_PRODUCT
SALES_DATA

table2
ID_PRODUCT
PRODUCT_DATA
ID_CATEGORY

table3
ID_CATEGORY
NAME_CATEGORY
CODE_CATEGORY
LEVEL

Categories in table2 have different levels, each level adds three extra digits. So level one 1 starts with '001', than level 2 has six '001001' level 3 '001001001' etc. What I need is a query the will give me SALES_DATA, PRODUCT DATA and NAME_CATEGORY from level 2, even if the product is on higher level. Say a product has CODE_CATEGORY 002005021, I need the NAME_CATEGORY of 002005, not NAME_CATEGORY of full 002005021. I know how to extract those digits:

SUBSTRING(tree.KOD_TREE,1,6)

but how do I make it show the NAME_CATEGORY connected with the 6-digit CODE_CATEGORY not its 9-digit one?

The code without that would look something like:

SELECT
table1.SALES_DATA
table2.PRODUCT_DATA
table3.NAME_CATEGORY
LVL2_NAME_CATEGORY /* (here I would put the NAME_CATEGORY from lower level) */

FROM
db.table1
INNER JOIN db.table2 ON table1.ID_PRODUCT = table2.ID_PRODUCT
INNER JOIN db.table3 ON table2.ID_CATEGORY = table3.ID_CATEGORY

Answer 1

Consider a LEFT JOIN on a table3 self join using the SUBSTRING() value:

SELECT 
       table1.SALES_DATA,
       table2.PRODUCT_DATA,
       table3.NAME_CATEGORY,
       levelsub.NAME_CATEGORY AS LOWER_LEVEL_CATEGORY

FROM db.table1 
INNER JOIN db.table2 ON table1.ID_PRODUCT = table2.ID_PRODUCT 
INNER JOIN db.table3 ON table2.ID_CATEGORY = table3.ID_CATEGORY
LEFT JOIN db.table3 AS levelsub 
       ON levelsub.CODE_CATEGORY = SUBSTRING(table3.CODE_CATEGORY,1,6)