Query error for ambiguous column name in SQLite

May 7, 2023

conn = sqlite3.connect(':memory:')
otvetili.to_sql('otvetili', conn, index=False)
init.to_sql('init', conn, index=False)

query = '''
        select
            t1.date,
            t1.ABONENT,
            t1.CONNID,
            t1.AGENT_INFsv,
            t1.NAMEsv,
            t1.Date_Time_Agent,
            t1.Date_Time_End,
            t2.AGENT_INFOstager,
            t2.NAMEstager,
            t2.Date_Time_Agent1inc,
            t2.Date_Time_End1inc
        from
            otvetili t1
            INNER join init t2 on t1.CONNID = t2.CONNID
            INNER join init t2 on t1.Date_Time_Agent > t2.Date_Time_Agent1inc
            INNER join init t2 on t1.Date_Time_Agent < t2.Date_TimeEnd1inc
        '''
df = pd.read_sql_query(query, conn)

ambiguous column name: t2.AGENT_INFOstager

I am using pandas to work with data and I needed sqlite3 to properly join two tables. These 2 dataframes got different names for AGENT_INFO column as you can see. However it still finishes with the error and I can’t find a single reason why is it happening.

>Solution :

Are you sure that you want to join 3 copies of init to otvetili?
If yes, then you should use different aliases for each copy.

But, I suspect that you only need a single join and merge the conditions of the ON clauses:

SELECT t1.date,
       t1.ABONENT,
       t1.CONNID,
       t1.AGENT_INFsv,
       t1.NAMEsv,
       t1.Date_Time_Agent,
       t1.Date_Time_End,
       t2.AGENT_INFOstager,
       t2.NAMEstager,
       t2.Date_Time_Agent1inc,
       t2.Date_Time_End1inc
FROM otvetili t1 INNER JOIN init t2 
ON t1.CONNID = t2.CONNID 
AND t1.Date_Time_Agent > t2.Date_Time_Agent1inc
AND t1.Date_Time_Agent < t2.Date_TimeEnd1inc;