I have one single table, logging site activity as a security precaution. I want to generate a report, including items of a certain type, but only if a seperate type has not taken place after the timestamp within the first type.
log_data_sig +--------+---------------------+-------+--------+------------------------------+ | eid | timestamp | type | user | extra | +--------+---------------------+-------+--------+------------------------------+ | 36625 | 2002-01-02 02:14:02 | join | 2875 | cloak.sli?data:a | | 36626 | 2002-01-02 02:14:07 | sign | 2875 | /data:a:cloak | +--------+---------------------+-------+--------+------------------------------+ The select should only select whole rows, where type is equal to "join", but a type "sign", on the same value of user, has not occured after the last timestamp of "open". sub-select imprementation used to be SELECT * FROM log_data_sig WHERE type="join" AND user=EXTERNAL_USER_ID AND timestamp <(SELECT timestamp FROM log_data_sig WHERE type="sign" AND user=EXTERNAL_USER_ID ORDER BY timestamp DESC LIMIT 1) ORDER BY timestamp DESC LIMIT 1 I only need to go back as far as the last time the type "join" was used by EXTERNAL_USER_ID, but he failed to use the type "sign" after. Idea being, that a user can't perform a "join" again, until he has perfored a "sign". If any1 can help me perform this within a single SELECT statment, it would help. Thanks in advance. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
