Search in reference table and insert in main table..hirerchy issue.

I have reference table and Main table.. in referencetable i have values like

root_name     down1_name    down2_name  
animal        birdie        NULL            
animal        doggie        companion   
animal        doggie        herding 
animal        doggie        hunting 
animal        gerbil        NULL    
mineral       gypsum        NULL    
mineral       silica        NULL    
vegetable     carrot        NULL    
vegetable     potato        NULL    
vegetable     rutabaga      NULL    
vegetable     NULL          NULL

in Main table

rootname      level1       level2       level3
carrot
vegetable
hunting

need to fill levels based on condition on rootnames

expecting is

rootname     level1       level2        level3
carrot       vegetable    carrot        NULL 
vegetable    vegetable    NULL          NULL 
hunting      animal       doggie        hunting

plz ..Thanks in Advance

asked Sep 28 '11 at 11:00 AM in Default

Mahesh
11 ● 1 ● 3

Can you suppliy your code where you have tried to resolve this?

Sep 29 '11 at 07:40 AM sp_lock

@Mahesh Please elaborate on what you need. I formatted your question, but still the final data does not make much sense yet. Are you sure that the data is correct?

Sep 29 '11 at 09:33 AM Oleg

In maintable rootname should search in all 3 columns reference table if it match in any one column the return root_name down1_name down2_name to main table to fill level1 level2 level3 accordingly.. ex:1 take vegetable in main table , it will search in reference table first column no luck,then second column no luck, then third column yes got it ,then return vegetable,null,null.ex:2 take carrot in main table , it will search in reference table first column no luck,then second column yes got it ,then return vegetable,carrot,null.

Sep 29 '11 at 11:17 AM Mahesh

(comments are locked)