How about something like this ? SELECT * FROM log_data_sig AS l1 LEFT JOIN log_data_sig AS l2 ON l1.user = l2.user WHERE l1.type="join" AND l1.user=EXTERNAL_USER_ID AND l2.type = "sign" AND l1.timestamp < l2.timestamp ORDER BY l1.timestamp DESC LIMIT 1;
Terje K > -----Original Message----- > From: PPSlim [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, January 02, 2002 4:52 AM > To: [EMAIL PROTECTED] > Subject: Do not understand a method without sub-selects > > > 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 > --------------------------------------------------------------------- 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
