Data between two tables.

Database
Enthusiast

Data between two tables.

Hi, I have to use only SQL (Not-Plsql) to input to my tool and I am not sure what function to use.

I have two table T1 and T2, need data from T1 for the corresponding T2 key value. 





Table T1  
Key Description
A1 Desc1
A2 Desc2
A3 Desc3
B1 Desc4
C1 Desc5





Table T2  
Key Col1
q1 A1,A2
q2 A1,B1,A3
q3 B1

Result ans:






Result ans set    
Key Col1 Description
q1 A1,A2 Desc1,Desc2
q2 A1,B1,A3 Desc1,Desc4,Desc3
q3 B1 Desc3

Please let me know what function to use in SQL .

4 REPLIES
Enthusiast

Re: Data between two tables.

Tables and requried results again, 

Table T1

Key        Description

A1          Desc1

A2          Desc2

A3          Desc3

B1          Desc4

C1          Desc5

Table T2

Key          Col1

q1            A1,A2

q2            A1,B1,A3

q3            B1

Result Table

Key            Col1           Description

q1              A1,A2         Desc1,Desc2

q2              A1,B1,A3    Desc1,Desc4,Desc3

q3              B1              Desc3

Thanks !!!

Teradata Employee

Re: Data between two tables.

Hi,

if you can use recursive query to walk through the distinct parts of each Col1 value, building the Description values along the way.   Here is a distantly similar topic about this:  http://forums.teradata.com/forum/database/help-in-recursive-query-required

Thank you,

Vlad.

Enthusiast

Re: Data between two tables.

For the required result, using SUBSTRING is enough but if the Col1 column having more number of commas i.e unpredicatable means that time you can go for Recursive query. using Resursive is best for alltime.

sel key, substr(col1,1,position(',' in col1)-1) A, substr(col1,position(',' in col1)+1) B, substr(B,1,position(',' in B)-1) C,

substr(B,position(',' in B)+1) D from Tab1

use col A,C and D in join condition to get the required result.

Fan

Re: Data between two tables.

Hi

What exactly is adhoc query and give an exmple?

Thanks in advance.