Search code examples
sqlclouderahue

sql, using join with renamed columns

I've got two tables with similarly named columns, and I want to do a join on them. So I have to rename the columns

Table1

|--------------------|------------------|
|      col1           |     col2         |
|--------------------|------------------|
|          1         |         5       |
|--------------------|------------------|
|          2         |         6       |
|--------------------|------------------|
|          3         |         7       |
|--------------------|------------------|
|          4         |         8       |

Table2

|--------------------|------------------|
|      col3          |     col2         |
|--------------------|------------------|
|          10        |         5       |
|--------------------|------------------|
|          11        |         6       |
|--------------------|------------------|
|          12        |         7       |
|--------------------|------------------|
|          13        |         8       |

What I want

|--------------------|------------------|------------------|
|      col1          |     col2         |     col3         |
|--------------------|------------------|
|          1         |         5       |         10       |
|--------------------|------------------|------------------|
|          2         |         6       |         11       |
|--------------------|------------------|------------------|
|          3         |         7       |         12       |
|--------------------|------------------|------------------|
|          4         |         8       |         13       |

But I can't seem to figure out how to order the code properly

select col1 as T1_col1,
      col2 as T1_col2
from Table1 as T1
inner join 
    (select col3 as T2_col3,
        col2 as T2_col2
     from Table2 as T2)
    on T1.T1_col2 = T2.T2_col2

what's the right way to do this?

Solution

  • Maybe I'm misunderstanding what you're asking but I believe that you just need to take the second table alias outside the parenthesis, everything else seems fine to me. 

    select T1.col1 as T1_col1,
  T2.col2 as T2_col2 --assuming you want col2 from table 2, otherwise your join would make no sense...
from Table1 as T1
inner join 
(select col3 as T2_col3,
    col2 as T2_col2
 from Table2)T2
on T1.col2 = T2.col2