'select cols from another table after left join

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
sqlsqliteleft-join


Solution 1:[1]

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;

Solution 2:[2]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 forpas
Solution 2

Related Questions