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;