Joining two tables based on specific column value

Question

I have two tables (Table 1 and Table 2) that I want to join them and create Table 3. The logic is the following:

Table 1

column1 | column2 | column3
user1      | ID1        | name1
user2      | ID2        | name2
user3      | ID3        | name3
user4      | ID4        | name4


Table 2

column1 | column2
user1      | ACT1    
user1      | ACT1    
user1      | ACT1    
user1      | ACT1    
user2      | ACT2    
user3      | ACT3    
user3      | ACT3    
user3      | ACT3    
user4      | ACT4    

I want the joined table to be this way:

Table 3

column1 | column2 | column3
user1      | ACT1     | ID1    
user1      | ACT1     | ID1    
user1      | ACT1     | ID1    
user1      | ACT1     | ID1    
user2      | ACT2     | ID2    
user3      | ACT3     | ID3    
user3      | ACT3     | ID3    
user3      | ACT3     | ID3    
user4      | ACT4     | ID4    

Basically, Table 3 is the same as Table 2. But, the corresponding ID for each user is fetched from Table1 and joined as a separate column. This process is repeated for all users.

Answer 1

You just need to use JOIN by column1 column from Table1 and Table2

select t2.*,t1.column2 as column3
from Table1 t1 
inner join Table2 t2 on t1.column1 = t2.column1  

[Results]:

| column1 | column2 | column3 |
|---------|---------|---------|
|   user1 |    ACT1 |     ID1 |
|   user1 |    ACT1 |     ID1 |
|   user1 |    ACT1 |     ID1 |
|   user1 |    ACT1 |     ID1 |
|   user2 |    ACT2 |     ID2 |
|   user3 |    ACT3 |     ID3 |
|   user3 |    ACT3 |     ID3 |
|   user3 |    ACT3 |     ID3 |
|   user4 |    ACT4 |     ID4 |

sqlfiddle