Query to join one column from another

Asked

Viewed 616 times

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 line separated by 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
mysql sql query select

  • What would be "group" and "camp"?

  • Hello, Group a team to which the user belongs, one can ignore, and Camp, where the user entered a line when he logged in, and another when he logged out. The system inserts a new line whenever there is a user interaction indicating, puts the date/time and if it is login or logout, I cannot change this, but I need to get a listing per campaign and per user of the time of login in a column and logout in another column.

  • I have information that’s in the same column in separate rows, that I need to turn into a different row and columns.

1 answer

4


I believe that this should work, I used only generic functions 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'

  • If the BD supports the use of CASE and a grouping function can be another solution, inform BD @Vitor Freitas

  • 1

    Hello, thank you very much. Ja I will test and give feedback. The server is mysql 5.1.

  • 1

    @Vitorfreitas Ajustei para Mysql

  • Success, thank you very, very much.

  • 1

    @Vitorfreitas Glad you liked the answers! But the best way to thank whoever helped you is to mark the best answer and vote for everyone who helped you. So you make sure that whoever wrote the answer gets something in return, in addition to making the site cleaner and more useful for everyone. Adding a new answer like this (which is not an answer to the question) makes the site more confusing and can get in the way. Please check How and why to accept an answer?

Browser other questions tagged mysql sql query select

You are not signed in. Login or sign up in order to post.