CODEWITHSUNDEEP
sqlpostgresql
user1224478
user1224478

Reputation: 355

How to take the result of one query and use that list as a key for another query on a second table?

My first query will return a list of users and their last login times:

username   |       last_login
john            2020-04-12 00:01:23 
amy             2020-04-10 12:13:42
brad            2020-04-11 11:21:00
max             2020-04-09 03:33:00

I would then like to search another table that has a record of all logins and their duration and return the longest login time for each user.

username    |    login_time          |      logout_time       |    duration
john          2020-04-12 00:01:23        2020-04-12 00:10:23          9
amy           2020-04-10 12:13:42        2020-04-10 12:43:42          30
brad          2020-04-11 11:21:00        2020-04-11 12:21:00          60
john          2020-04-10 08:21:00        2020-04-10 08:45:00          24
amy           2020-04-10 07:00:42        2020-04-10 07:03:42          3
max           2020-04-09 03:33:00        2020-04-09 03:40:00          7

Query 1:

select username, last_login from t1;

Query 2 to get longest streak for a specific user:

select duration from t2 where username='john' and duration=(select max(duration) from t2 where username='john')

The result I would like is each user, their last login, and there longest login time:

username    |        last_login         |    longest_login
john             2020-04-12 00:01:23                24
amy              2020-04-10 12:13:42                30
brad             2020-04-11 11:21:00                60
max              2020-04-09 03:33:00                7

I'm not really sure how to perform the 2nd query using each username found in query 1 and then combine the result.

Upvotes: 0

Views: 21

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can join and aggregation:

select q1.username, q1.lastlogin, max(t.duration)
from firstquery q1 join
     othertable t
     on t.username = q1.username
group by q1.username, q1.lastlogin;

Upvotes: 1

Related Questions