x

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

more ▼

asked Sep 28, 2011 at 11:00 AM in Default

avatar image

Mahesh
11 2 2 3

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

Sep 29, 2011 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, 2011 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, 2011 at 11:17 AM Mahesh
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1066

asked: Sep 28, 2011 at 11:00 AM

Seen: 593 times

Last Updated: Sep 29, 2011 at 09:31 AM

Copyright 2016 Redgate Software. Privacy Policy