Query to join one column from another

Navigation
6

I have tried unsuccessfully to get a result.

I have the table with the following Login and Logout information: 

|Group        |User    |EventDate              |Event        |Camp
------------------------
|Evolution    |1012    |2014-07-07 10:36:04    |LOGIN        |AUTENTI1

|Evolution    |1012    |2014-07-07 10:38:18    |LOGOUT       |AUTENTI1

|Evolution    |1012    |2014-07-07 10:38:28    |LOGIN        |AUTENTI1

|Evolution    |1012    |2014-07-07 10:39:21    |LOGOUT       |AUTENTI1

|Evolution    |1012    |2014-07-07 10:41:46    |LOGIN        |EVOL1

|Evolution    |1012    |2014-07-07 13:09:53    |LOGOUT       |EVOL1

|Evolution    |1012    |2014-07-07 13:11:19    |LOGIN        |EVOL1

|Evolution    |1012    |2014-07-07 13:39:49    |LOGOUT       |EVOL1

How do I get Login and Logout on the same separate line per campaign. 

|Group        |User    |Login                  |LogOut               |Camp
--------
|Evolution    |1012    |2014-07-07 10:36:04    |2014-07-07 10:38:18  |AUTENTI1

|Evolution    |1012    |2014-07-07 10:38:28    |2014-07-07 10:39:21  |AUTENTI1

|Evolution    |1012    |2014-07-07 10:41:46    |2014-07-07 13:09:53  |EVOL1

|Evolution    |1012    |2014-07-07 13:11:19    |2014-07-07 13:39:49  |EVOL1
    
asked by anonymous 08.07.2014 / 17:40

1 answer

4

I believe that this should work, I used generic functions only because it was not specified which SGDB is using 

SELECT
  A.group,
  A.user,
  A.eventdate as login,
  (SELECT 
      B.eventdate
   FROM 
      TABELA B 
   WHERE 
      B.event = 'LOGOUT' AND
      B.user = A.user AND 
      B.group = A.group AND 
      B.camp = A.camp AND 
      B.eventdate > A.eventdate
   LIMIT 1) as logout,
  A.camp
FROM
  TABELA A
WHERE
  A.event = 'LOGIN'
    
08.07.2014 / 18:10
MySQL database update How to do an optimization with restriction of inequality?