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

Mahesh gravatar image

Mahesh
11 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 oldest
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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x985

asked: Sep 28, 2011 at 11:00 AM

Seen: 418 times

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