Query for lookup in two columns

Database
Fan

Query for lookup in two columns

Hi i'm new in teradata i have the fllowing case:

I have a query which return matching between 2 shops (same shop can match with multiple shops):

Store_id_A   store_id_B

1                    4            

2                    1

3                     2

5                     7

 

I would like to lookup in this result to create a company_id column like which follow this logic:

If one shop is matching with another shop then they should have the same company_id :

in our example first 3 rows and last row  should have same Group_store_id(1) because they involved store_id: 10 (col A or B) and as store_id 2 is matching with store_id 10(row 3) then last row should have same Group_store_id(1) because store_id 2

Store_id_A   store_id_B          Group_store_id

10                    4                        1

10                    9                        1

2                    10                        1

3                    6                        2

5                    7                        3

9                    2                        1 

 

Is there a way to implement this in Teradata SQL assistant?

Tags (1)
1 REPLY
Fan

Re: Query for lookup in two columns

I noticed my example is not correct:

here is the case

Store_id_A   store_id_B

10                  4            

10                  9

2                    10

3                     6

5                     7

9                     10

 

and what i would like as a result

Store_id_A   store_id_B          Group_store_id

10                    4                        1

10                    9                        1

2                    10                        1

3                     6                         2

5                     7                         3

9                     2                         1