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

i'm not sure how it, let me try simple example. have 3 tables 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 3 digits. level 1 one starts '001', level 2 has 6 '001001' level 3 '001001001' etc. need query give me sales_data, product data , name_category level 2, if product on higher level. product has code_category 002005021, need name_category of 002005, not name_category of full 002005021. know how extract digits:

substring(tree.kod_tree,1,6) 

but how make show name_category connected 6-digit code_category not 9-digit one?

the code without like:

select table1.sales_data table2.product_data table3.name_category lvl2_name_category /* (here put name_category lower level) */  db.table1 inner join db.table2 on table1.id_product = table2.id_product inner join db.table3 on table2.id_category = table3.id_category 

consider left join on table3 self join using substring() value:

select         table1.sales_data,        table2.product_data,        table3.name_category,        levelsub.name_category lower_level_category  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 levelsub         on levelsub.code_category = substring(table3.code_category,1,6)