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

Поиск
Список
Период
Сортировка
От Sill-II, Stephen
Тема look for latest user login in one table from a list of users in a nother
Дата
Msg-id 4BAE87828F06EB45B2641B83D370350F4663@ostex002.ad.ost.dot.gov
обсуждение исходный текст
Ответы Re: look for latest user login in one table from a list of  (Achilleus Mantzios <achill@matrix.gatewaynet.com>)
Список 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




В списке pgsql-sql по дате отправления:

Предыдущее
От: Andreas Schmitz
Дата:
Сообщение: Re: Create function statement with insert statement
Следующее
От: Achilleus Mantzios
Дата:
Сообщение: Re: look for latest user login in one table from a list of