CODEWITHSUNDEEP
mysqlsql
bsautner
bsautner

Reputation: 4802

mysql join table, return only rows based on timestamp comparison

I have a table in a mysql database like: (simplified to make my point)

PARENT
ID | EXPIRE | ITEM
0  | 1000   | FOO
1  | 2000   | BAR

And a another with a one to many relationship with table A 

ITEMS
    ID | FK | TIMESTAMP 
    0  | 0  | 1488555058 
    1  | 0  | 1488555059
    2  | 0  | 1488555060

Consider the ITEMS table is being continuously populated with new values with the current timestamp. I want to query the db and only return records in which The ITEMS table does not contain a record with a TIMESTAMP >= CURRENT_TIME - EXPIRE_TIME - so the result will only be PARENTs that have not received a new ITEM since the EXPIRE time lapsed.

I wrote this which doesn't work: 

   select P.ID
    from PARENT as P
    JOIN     ITEMS as I
    ON       I.FX =
             (
             SELECT  I2.FK
             FROM    ITEMS as I2
             WHERE   P.ID = I2.FK
             AND  ((((UNIX_TIMESTAMP()* 1000) - I2.TIMESTAMP)))  > P.EXPIRE
             ORDER BY TIMESTAMP DESC
             limit 1
             )

What I want is zero rows returned if every PARENT as an ITEM with a TIMESTAMP > than (UNIX_TIMESTAMP - EXPIRE) and a single record for each ITEM without one.

Stuck - any help would be appreciated!

EDIT - based on the answer below this is the working query - what i needed here was a result that contains items that didn't not have an item newer than the expire time and to flag them for batch processing. 

UPDATE PARENT P
SET BATCHID = ?
where not exists(
    select 1 from ITEMS V
    WHERE   V.FK = P.ID
    AND ((((UNIX_TIMESTAMP() * 1000) -  V.TIMESTAMP) / 1000) < P.EXPIRE)
    AND P.BATCHID is NULL
    ORDER BY P.PROCESSEDTIMESTAMP ASC

)

Upvotes: 0

Views: 87

Answers (1)

Anand
Anand

Reputation: 1123

That is a confusing join statement. I would use not exists like this:

select *
from Parent as p
where not exists (
    select 1
    from Items
    where FK = p.ID
    and TIMESTAMP  > ((UNIX_TIMESTAMP()* 1000) - p.EXPIRE)
    )

Upvotes: 2

Related Questions