CODEWITHSUNDEEP
sql-serverjoin
ARUN
ARUN

Reputation: 31

Sql Inner join or self join

I have an sql table populated from a program and the data would look like this:

Primary key - Request ID columns

Request ID  Session ID UserID LoginOperation     Time

1               1      Arun       Logon          8:00
2               1      Arun       Logoff         8:30
3               2      Sandy      Logon          7:55
4               2      Sandy      Logon Expired  8:38

Now what I need is 

Request ID  Session ID UserID  LoginOperation    Time      Login Operation  Time

1              1       Arun    Logon             8:00      Logoff           8:30
3              2       Sandy   Logon             7:55      LogonExpired     8:38

SO I will need my logon information along with the log of time in a same row.

How can I achieve this?

Should I filter my 1st table with logon and then do the same for the second table by either logoff or logonexpired and then inner join both tables on session id. ?

Or can I do a self join on these tables with some where condition..

Please let me know your suggestions?

Hi ,

I have come up with the below query to get my log on information along with the log off time.

Select T.COGIPF_LOCALTIMESTAMP, T.COGIPF_SESSIONID, T.COGIPF_REQUESTID,
       T.COGIPF_STATUS, T.COGIPF_LOGON_OPERATION, T.COGIPF_USERNAME,
       T.COGIPF_USERID, T.COGIPF_NAMESPACE, T.COGIPF_CAMID,
       Z.COGIPF_LOCALTIMESTAMP as LogOffTime 
from [CognosAuditSampleDev].COGIPF_USERLOGON T 
    LEFT JOIN [CognosAuditSampleDev].COGIPF_USERLOGON Z on
        T.COGIPF_SESSIONID = Z.COGIPF_SESSIONID and
        Z.COGIPF_LOGON_OPERATION <>'Logon' 
where T.COGIPFSTATUS='Success' And Z.COGIPFSTATUS ='Success' And
      T.COGIPF_LOGON_OPERATION='Logon'

I found an issue when there is no logg off received for a logon , in this case the logon record is missing in my table even after I do a left join. Please let me know where I am going wrong.

Thanks, Manikandan

Upvotes: 3

Views: 189

Answers (3)

John Odom
John Odom

Reputation: 1213

You can do this easily with a self INNER JOIN and comparing the sessionIDs and requestIDs. For example:

SELECT t1.RequestID, t1.SessionID, t1.UserID, t1.LoginOperation, t1.Time, 
       t2.LoginOperation, t2.Time
FROM Test t1
    INNER JOIN Test t2 on t2.SessionID = t1.SessionID
WHERE t1.RequestID < t2.RequestID

This has been tested on SQL Fiddle.

Enjoy!

Upvotes: 1

jpw
jpw

Reputation: 44921

If a session only can have a single logon and logoff event (that is, if each logon starts a new session) then you could skip the join altogether and use conditional selection and the max() function to flatten the result:

SELECT 
    MAX(CASE WHEN LoginOperation = 'Logon' THEN RequestID END) RequestID, 
    SessionID, UserID, 
    MAX(CASE WHEN LoginOperation = 'Logon' THEN LoginOperation END) LoginOperation1, 
    MAX(CASE WHEN LoginOperation = 'Logon' THEN Time END) Time1, 
    MAX(CASE WHEN LoginOperation <> 'Logon' THEN LoginOperation END) LoginOperation2,
    MAX(CASE WHEN LoginOperation <> 'Logon' THEN Time END) Time2 
FROM your_table
GROUP BY SessionID, UserID

And if we can assume that logon always happens before logoff and that there will always be a pair then you could reduce it further to:

SELECT 
    MIN(RequestID) RequestID, 
    SessionID, UserID, 'Logon' as LoginOperation1, 
    MIN(Time) Time1, 
    MAX(CASE WHEN LoginOperation <> 'Logon' THEN LoginOperation END) LoginOperation2,
    MAX(Time) Time2 
FROM your_table
GROUP BY SessionID, UserID

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 416131

SELECT r1.RequestID, r1.SessionID, r1.UserId, r1.LoginOperation, r1.Time
    , r2.LoginOperation, r2.Time 
FROM Requests r1
INNER JOIN Requests r2 on r2.SessionID = r1.SessionID and r2.LoginOperation <> 'Logon'
WHERE r1.LoginOperation = 'Logon'

You already mentioned a self-join in the question... you should just try it :)

Upvotes: 7

Related Questions