Map results from one table to different columns in another query

I’m trying to retrieve a set of records from table that has descriptions on another table in same database.

1. Table 1:
   ColA1 | DescrpA1
   — A – – – – Apple
   — B – – – – Orange
   — C – – – – Banana
   — D – – – – Watermelon
   

2. Table 2:
   ColA2 | Qty1 | ColB2 | Qty2 |
   –A – – – – – 1 – – – C – – – – 1
   –C – – – – – 1 – – – D – – – – 2
   –B – – – – – 1 – – – A – – – – 1
   

What I want is a way to map descriptions from Table1 into results of Table2

3. Result:
   ColA2 | Decription | Qty1 | ColB2 | Description | Qty2
   –A – – – – Apple – – – – -1 – – – – C – – – Banana – – – – 1
   –C – – – – Banana – – – 1 – – – – D – – – Watermelon – 2
   –B – – – – Orange – – – 1 – – – – A – – – Apple – – – – – – 1
   

Sorry about the formatting. first time posting a questi

Answer

You want to join the tables . . . twice:

select t2.cola2, t1_a.DescrpA1, t2.colb2, t1_b.DescrpA1
from table2 t2 join
     table1 t1_a
     on t2.cola2 = t1_a.cola1 join
     table1 t1_b
     on t2.colb2 = t1_b.cola1;