select cols from another table after left join-CodePudding

After running this query on df1,

"SELECT 'max' type, user, MAX(daytime) time FROM df1 UNION ALL SELECT 'min' type, user, MIN(daytime) time FROM df1")

I get something like this:

type    user     time
max     a        679..
min     b        12..

I have another dataset like this:

id      email
a       [email protected]
b       [email protected]

I want to join them such that my end result is this:

type    user     time      email
max     a        679..     [email protected]
min     b        12..      [email protected]

ideally in the same query. I tried this:

"SELECT 'max' type, user, MAX(daytime) time FROM df1 UNION ALL SELECT 'min' type, user, MIN(daytime) time FROM df1 LEFT JOIN df2 ON df2.id == df1.user")

till here, it doesn't throw an error but as soon as I select the cols from df2,

"SELECT email, 'max' type, user, MAX(daytime) time FROM df1 UNION ALL SELECT 'min' type, user, MIN(daytime) time FROM df1 LEFT JOIN df2 ON df2.id == df1.user")

I get errors that:

PandaSQLException: (sqlite3.OperationalError) no such column: email

CodePudding user response：

You must join the result of the union as a subquery to df2:

SELECT t.*, df2.email 
FROM (
  SELECT 'max' type, user, MAX(daytime) time FROM df1 
  UNION ALL 
  SELECT 'min' type, user, MIN(daytime) time FROM df1 
) t LEFT JOIN df2 ON df2.id = t.user;

CodePudding user response：

You cann join only two tables and combine hem into another select

SELECT type,    df3.user,     time,      email
FROM
 (SELECT 'max' type, user, MAX(daytime) time FROM df1 UNION ALL SELECT 'min' type, user, MIN(daytime) time FROM df1) df3
LEFT JOIN df2 ON df2.id == df3.user