Select most recent event for each plate on a property unless the most recent is "Out" - self-join?

Question

My cameras are positioned on the entry and exit gates of a parking garages. As cars pass the cameras, they capture the license plate and other attributes and load it into a database in real time. I want to perform a single query that will tell me the license plates of all cars that are still in a given property at the time of the query. This would be a real-time, on-site inventory of all that are on site now.

So, if at 10:00am I see ABC123 come in the "In" camera (direction = "In"), it should be included in the results unless I see a later event when that same plate was recorded leaving the property (direction = "Out").

I have a table called "events" where each time a license plate enters or leaves, I collect the timestamp (camtimestamp), the license plate (plate), the property (propUID) and the direction (direction = either "In" or "Out").

To make matters interesting, I have multiple properties, but I want to be able to query only one property at a time.

Partial table structure

Here is a partial structure for that table:

CREATE TABLE events (
 eventUID          INT NOT NULL AUTO_INCREMENT,
 progtimestamp        DATETIME,
 camtimestamp         DATETIME,
 lplate            VARCHAR(255) NOT NULL,
 picfilename       VARCHAR(255) NOT NULL,
 camname           VARCHAR(255) NOT NULL,
 direction         VARCHAR(10),
 lane              VARCHAR(10),
 propUID                   INT,
 ownerUID                  INT,
 ......
);

How would I perform a self-join SELECT that would give me the list of license plates that are on a given property? Again, it seems that if the most recent event for a given plate is direction="In" then it is on site. If the most recent event for a plate is direction="Out" then it is not on site.

My considerations so far

My first thought was to run a query that gave me just the events at the property and load that whole result into an array and then run through the array event by event in reverse chronological order take the plate and query a table for that plate’s presence in the table. If the new information is that the plate is out, then put that into the table. If the new information is that the plate is in, then update the event time. To me this is clunky and resource intensive. It would be better if I could get a single query to give the result, but I am new to the concept of join queries.

I need the most recent event per plate (which I think I have above). But if the most recent event is “Out” then I need to eliminate that plate from the list of currently on site license plates. I could SELECT only the most recent events that are “In” (e.g. non-Out), but that would get me the list of all plates that have ever been on the property from the beginning. I need only those that are “In” with no successive matching “Out” to find those on site now.

Basically I believe that if I could redirect the output from the query above into another SELECT query that would eliminate the “Out”s then I would have what I need.

Query logic

The logic for this is as follows:

1. List all events on a given property (where propUID = X)
2. Take only the most recent entry (max(camtimestamp)) for each plate
3. Discard where Direction = "Out".

The result of the three above is the list of license plates on site right now.

This query below takes care of points one and two. I just need to get rid of all the ones where direction = "Out" from this list of results.

SELECT *
                FROM events WHERE (lplate,camtimestamp) IN
                ( SELECT lplate, MAX(camtimestamp)
                  FROM events
                  WHERE propUID = 1
                  GROUP BY lplate
                )
                  ORDER BY camtimestamp DESC

Can I nest/join the above query into another SELECT ... where direction="In"?

Query that didn't work

This didn't work:

    SELECT * from  
     (SELECT * FROM events WHERE (lplate,camtimestamp) 
     IN ( SELECT lplate, MAX(camtimestamp) 
     FROM events WHERE propUID = 1 GROUP BY lplate ) 
    ORDER BY camtimestamp DESC) 
    WHERE direction='In'

It produced this error:

  ERROR: Could not able to execute SELECT * FROM events 
  WHERE (lplate,camtimestamp) IN ( SELECT lplate, MAX(camtimestamp) 
  FROM events WHERE propUID = 1 GROUP BY lplate ) 
  ORDER BY camtimestamp DESC. 

Every derived table must have its own alias

Answer 1

Would this work?

select distinct
    ev.lplate
from
    events ev
where
    ev.lplate not in (select lplate from events ev where ev.direction = 'out' )

I modified it below and I hope this will work for you. It does an additional set of selections to get the most-recent in and out times, then compares them to see if the most-recent in time is after the most-recent out time.

Attempt # 2:

select distinct
    ev.lplate
from
    events ev
where
    ev.lplate not in (select lplate from events ev where ev.direction = 'out' )

or

ev.lplate in (

    select

        /*in_times.tm as 'in_time',
        out_times.tm as 'out_time',*/
        in_times.lplate
    from 
    (
        /*
        get all the most-recent out times
        */
        select 
            max(ev.progtimestamp) as 'tm', 
            ev.lplate, 
            ev.direction

        from 
            events ev 

        where 
            ev.direction = 'out'
        group by ev.lplate
        order by ev.direction, ev.progtimestamp desc
    )out_times

    left join
    (
        /*
        get all the most-recent in times
        */
        select 
            max(ev.progtimestamp) as 'tm', 
            ev.lplate, 
            ev.direction

        from 
            events ev 

        where 
            ev.direction = 'in'
        group by ev.lplate
        order by ev.direction, ev.progtimestamp desc
    )in_times on in_times.lplate = out_times.lplate
    where
        in_times.tm > out_times.tm

)