psylosss psylosss - 3 months ago 13
MySQL Question

Mysql: how to find users that currently in the room?

I have table

entrances
that logs times when users came into the room and came out of the room. Something like that:

user | action | time
-------------------------------------------
Ivan | in | 2016-08-28 12:00:00
John | in | 2016-08-28 12:00:01
Ann | in | 2016-08-28 12:00:02
Ivan | out | 2016-08-28 12:00:03
Ivan | in | 2016-08-28 12:00:04
Ann | out | 2016-08-28 12:00:05
Ivan | out | 2016-08-28 12:00:06
Mike | in | 2016-08-28 12:00:07
John | out | 2016-08-28 12:00:08
Ann | out | 2016-08-18 12:00:09
John | in | 2016-08-18 12:00:10
John | out | 2016-08-18 12:00:11
Ann | in | 2016-08-18 12:00:12


Users actions are independent. Only is known is that first action is always
in
and user cannot
in
twice witout
out
(and reverse).

My goal is to find all users that currently in room.

I have two ideas:


  1. select users that have no
    out
    after latest
    in

  2. select users that has count
    in
    more that count
    out



How to implement this on mysql? Or any other ideas?

SQL for testing:

CREATE TABLE `entrances` (
`id` int(11) NOT NULL,
`user` varchar(10) COLLATE utf8_bin NOT NULL,
`action` varchar(3) COLLATE utf8_bin NOT NULL,
`time` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO `entrances` (`id`, `user`, `action`, `time`) VALUES
(1, 'Ivan', 'in', '2016-08-28 12:00:00'),
(2, 'John', 'in', '2016-08-28 12:00:01'),
(3, 'Ann', 'in', '2016-08-28 12:00:02'),
(4, 'Ivan', 'out', '2016-08-28 12:00:03'),
(5, 'Ivan', 'in', '2016-08-28 12:00:04'),
(6, 'Ann', 'out', '2016-08-28 12:00:05'),
(7, 'Ivan', 'out', '2016-08-28 12:00:06'),
(8, 'Mike', 'in', '2016-08-28 12:00:07'),
(9, 'John', 'out', '2016-08-28 12:00:08'),
(10, 'Ann', 'out', '2016-08-28 12:00:09'),
(11, 'John', 'in', '2016-08-28 12:00:10'),
(12, 'John', 'out', '2016-08-28 12:00:11'),
(13, 'Ann', 'in', '2016-08-28 12:00:12');
ALTER TABLE `entrances` ADD PRIMARY KEY (`id`);

Answer
  1. First get the last action time from each user in the subquery
  2. Then join against that subquery to have only the last record for each user
  3. Then take only those records having the action = in with the where clause

Like this

select e.*
from entrances e
join
(
   select user, max(time) as mtime
   from entrances
   group by user
) t on t.user = e.user 
   and t.mtime = e.time
where e.action = 'in'