look for latest user login in one table from a list of users in a nother

From: "Sill-II, Stephen" <Stephen(dot)Sill-II(at)ost(dot)dot(dot)gov>
To: pgsql-sql(at)postgresql(dot)org
Subject: look for latest user login in one table from a list of users in a nother
Date: 2003-03-14 16:35:14
Message-ID: 4BAE87828F06EB45B2641B83D370350F4663@ostex002.ad.ost.dot.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I'm trying to come up with an efficient way to do the following task, but I
haven't found the correct join syntax to do it.

I have table "users" for keeping a list of users I need to check logins for.
It contains the following fields:

id,Full-Name,User-Name

I have table called "logs" that contains the actual radius log data. The
three fields I am concerned with are:

User-Name,Date,Time

I have gotten thus far almost what I want with the following query.

SELECT users."User-Name",max(logs."Date") as login_date,max(logs."Time") as
login_time FROM logs where logs."User-Name"=users."User-Name" GROUP BY
users."User-Name";

This gives me the User-Name, date, and time of people WHO HAVE LOGGED IN.
I'm looking to have a query that returns all of the users in the first
table, including those who have not logged in. I have an external perl
script that generates a nice html report for the manager. I have a script
that parses the raw .csv files, but I'm trying to move it entirely to
postgres, including if possible stored-procedures in plperl. I already have
a perl script that imports the raw log files into the logs table.

Am I on the right track?

Thanks,

Stephen Sill II

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Jean-Luc Lachance 2003-03-14 17:13:35 Re: look for latest user login in one table from a list of users
Previous Message Christoph Haller 2003-03-14 15:05:29 Re: Create function statement with insert statement