Tables being queried with SQL and Sqlalchemy have same column names

I am querying a variety of different tables in a mysql database with sqlalchemy, and sql query code.

My issue right now is renaming some of the columns being joined. The queries are all coming into one dataframe.

SELECT *
FROM original

LEFT JOIN table1 
    on original.id = table1.t1key

LEFT JOIN table2 
    on original.id = table2.t2key

LEFT JOIN table3
    on original.id = table3.t3key;

All i actually want to get from those tables is a single column added to my query. Each table has a column with the same name. My approach to using an alias has been as below,

table1.columnchange AS 'table1columnchange'
table2.columnchange AS 'table2columnchange'
table3.columnchange AS 'table3columnchange'

But the variety of ways I’ve tried to implement this ends up with annoying errors.

I am querying around 20 different tables as well, so using SELECT * at the beginning while inneficient is ideal for the sake of ease.

The output I’m looking for is a dataframe that has each of the columns I need in it (which I am trying to then filter and build modeling with python for). I am fine with managing the query through sqlalchemy into pandas, the alias is what is giving me grief right now.

Thanks in advance,

Answer

You can use nested queries:

SELECT
 original.column1 as somename,
 table1.column1 as somename1,
 table2.column1 as somename2
FROM
 (SELECT
    column1
 FROM
    original
 ) original
 LEFT JOIN (
    SELECT 
        column1
    FROM
        table1
 ) table1 ON original.id = table1.t1key
 LEFT JOIN (
    SELECT 
        column1
    FROM
        table2
 ) table2 ON original.id = table2.t2key

Advertisement

Answer