edit: have added @ bottom clearer example of mean.
here query, schema , results (can view on sqlfiddle if needed):
mysql 5.6 schema setup:
create table if not exists `transactions` ( `id` int(11) not null auto_increment, `effective_sn` int(11) default null, `serial_num` int(11) default null, `actor` varchar(100) default null, `type` varchar(20) default null, `trans_data` text, `trans_date` int(11) default null, `trans_prev` text, `content` text, primary key (`id`), key `effective_sn` (`effective_sn`), key `serial_num` (`serial_num`), key `type_index` (`type`), key `trans_date_index` (`trans_date`), fulltext key `content` (`content`) ) engine=myisam default charset=latin1 auto_increment=1093579 ; insert `transactions` (`id`, `effective_sn`, `serial_num`, `actor`, `type`, `trans_data`, `trans_date`, `trans_prev`, `content`) values (1091622, 100628, 100628, 'jhvisser', 'comment', '', 1435864188, null, 'some comment'), (1091749, 100628, 100628, 'jhvisser', 'comment', '', 1435926407, null, 'some comment'), (1092012, 100628, 100628, 'jhvisser', 'comment', '', 1436189497, null, 'some comment'), (1092085, 100628, 100628, 'jhvisser', 'comment', '', 1436199139, null, 'some comment'), (1092162, 100628, 100628, 'jhvisser', 'comment', '', 1436205996, null, 'some comment'), (1092240, 100628, 100628, 'jhvisser', 'comment', '', 1436211675, null, 'some comment'), (1092252, 100628, 100628, 'jhvisser', 'comment', '', 1436213410, null, 'some comment'), (1092288, 100628, 100628, 'jhvisser', 'comment', '', 1436217645, null, 'some comment'), (1092376, 100628, 100628, 'jhvisser', 'comment', '', 1436277006, null, 'some comment'), (1093530, 100628, 100628, 'jhvisser', 'time_worked', '60', 1436551662, null, null), (1093531, 100628, 100628, 'jhvisser', 'comment', '', 1436551662, null, 'some comment'); query 1:
select * `transactions` `serial_num` = 100628 , (type='comment' or type='time_worked') | id | effective_sn | serial_num | actor | type | trans_data | trans_date | trans_prev | content | |---------|--------------|------------|----------|-------------|------------|------------|------------|--------------| | 1091622 | 100628 | 100628 | jhvisser | comment | | 1435864188 | (null) | comment | | 1091749 | 100628 | 100628 | jhvisser | comment | | 1435926407 | (null) | comment | | 1092012 | 100628 | 100628 | jhvisser | comment | | 1436189497 | (null) | comment | | 1092085 | 100628 | 100628 | jhvisser | comment | | 1436199139 | (null) | comment | | 1092162 | 100628 | 100628 | jhvisser | comment | | 1436205996 | (null) | comment | | 1092240 | 100628 | 100628 | jhvisser | comment | | 1436211675 | (null) | comment | | 1092252 | 100628 | 100628 | jhvisser | comment | | 1436213410 | (null) | comment | | 1092288 | 100628 | 100628 | jhvisser | comment | | 1436217645 | (null) | comment | | 1092376 | 100628 | 100628 | jhvisser | comment | | 1436277006 | (null) | comment | | 1093530 | 100628 | 100628 | jhvisser | time_worked | 60 | 1436551662 | (null) | (null) | | 1093531 | 100628 | 100628 | jhvisser | comment | | 1436551662 | (null) | comment | in query showing time_worked type can see referring to. don't want comments appearing in results if there time_worked type record created within 5 minutes before or after time comment record created.
this may confusing, idea simple i'll explain example case. working time tracking application. lot of times people make comments on items , forget log time worked. idea want find comments time not logged. people realize forgot , add time worked few minutes later, why can created 5 minutes before or after.
looking @ results in image above there 2 results should not there, , last 2 time added when comment made.
is possible in sql?
edit:
input: sql query 1 above. output: want modify existing query have rid of "comment" types have associated "time_worked" type. when referring type, referring actual column type, has data comment or time_worked within it. if there time_worked type , comment same trans_date (unix time) should not appear in results.
so in example data gave last 2 rows not showing.
what doing looking records associated record in same table not exist type. purpose, use left self join:
select comments.*, date_sub(from_unixtime(comments. trans_date), interval 5 minute), timeworked.id transactions comments left join transactions timeworked on comments. serial_num = timeworked.serial_num , from_unixtime(comments.trans_date) between date_sub(from_unixtime(timeworked. trans_date), interval 5 minute) , date_add(from_unixtime(timeworked. trans_date), interval 5 minute) , timeworked.type = 'time_worked' comments.type = 'comment' , timeworked.id null 
Comments
Post a Comment