Mysql to use the same table row in two columns

Question

I have a table called Notifications where status=1 is EntryTime and status=2 is ExitTime.

  +-----+------+------+------+--------+
  | n_id| notification_time   | status | 
  +-----+------+------+------+--------+
  |  1  | 2016-11-17 16:44:02 |   2    |
  |  1  | 2016-11-17 16:43:55 |   1    |
  |  2  | 2016-11-17 13:05:47 |   1    |
  |  2  | 2016-11-17 13:08:29 |   2    |
  |  4  | 2016-11-17 14:09:02 |   1    |
  |  4  | 2016-11-17 14:13:45 |   2    |
  |  1  | 2016-11-17 22:05:02 |   1    |
  |  1  | 2016-11-17 22:09:12 |   2    |
  +----+------+------+-------+---------+

I would need a result like

  +-----+------+------+------+--------+--------+--------+
  | n_id| Entry_time          | Exit_time               | 
  +-----+------+------+------+--------+--------+--------+
  |  1  | 2016-11-17 16:43:55 |   2016-11-17 16:44:02   |
  |  2  | 2016-11-17 13:05:47 |   2016-11-17 13:08:29   |
  |  4  | 2016-11-17 14:09:02 |   2016-11-17 14:13:45   | 
  |  1  | 2016-11-17 22:05:02 |   2016-11-17 22:09:12   |
  +----+------+------+-------+---------+-------+--------+

Any suggestions on how to do this? Thanks very much!

good question. If you have more than 2 entries by id, the min() and max() doesn't will help — Joao Polo
– Joao Polo, Commented Nov 21, 2016 at 20:34
Yes, n_id could be repetitive. I have another primary key column which i haven't mentioned in the tabular data above. — Anusha
– Anusha, Commented Nov 21, 2016 at 20:36
well add the other primary key or the rules for join the correct rows - — ScaisEdge
– ScaisEdge, Commented Nov 21, 2016 at 20:37

Joao Polo · Accepted Answer · 2016-11-21 20:33:09Z

2

You can try to group:

select n_id
    , min(notification_time) as entry_time
    , max(notification_time) as exit_time
from Notifications
group by n_id

answered Nov 21, 2016 at 20:33

Joao Polo

2,1631 gold badge17 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ScaisEdge · Accepted Answer · 2016-11-21 20:35:23Z

1

If you have always a couple of distinct n_id you can use a sellf join based on alias

  select a.n_id, a.notification_time as Entry_time, a.notification_time as Exit_time
  from my_table a 
  inner join my_table b on a.n_id = b.n_id
  where a.status = 1
  and b.status = 2

if n_id is repetitive need others column for correct match between rows

answered Nov 21, 2016 at 20:35

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Collectives™ on Stack Overflow

Mysql to use the same table row in two columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related