so im sure this won't tax anyone but new to all this! looking for a way to pull out data from an audit table which gives a view of all the created rows where they have not subsequently been deleted. so by example...
CREATE TABLE `test1` (
`tID` INT(11) NULL DEFAULT NULL,
`tDate` DATE NULL DEFAULT NULL,
`tName` VARCHAR(50) NULL DEFAULT NULL,
`tType` VARCHAR(50) NULL DEFAULT NULL,
`tAction` VARCHAR(50) NULL DEFAULT NULL
)
INSERT INTO `test1` VALUES (1, '2019-02-01', 'Bob', 'a', 'Create');
INSERT INTO `test1` VALUES (2, '2019-02-02', 'Frank', 'a', 'Create');
INSERT INTO `test1` VALUES (3, '2019-02-03', 'Jim', 'b', 'Create');
INSERT INTO `test1` VALUES (4, '2019-02-04', 'Frank', 'a', 'Delete');
INSERT INTO `test1` VALUES (5, '2019-02-05', 'Bob', 'b', 'Create');
INSERT INTO `test1` VALUES (6, '2019-02-06', 'Bob', 'a', 'Delete');
INSERT INTO `test1` VALUES (7, '2019-02-07', 'Bob', 'a', 'Create');
INSERT INTO `test1` VALUES (8, '2019-02-08', 'Frank', 'b', 'Create');
INSERT INTO `test1` VALUES (9, '2019-02-09', 'Bob', 'b', 'Delete');
INSERT INTO `test1` VALUES (10, '2019-02-10', 'Bob', 'b', 'Create');
INSERT INTO `test1` VALUES (11, '2019-02-11', 'kate', 'a', 'Create');
INSERT INTO `test1` VALUES (12, '2019-02-12', 'kate', 'a', 'Delete');
Want the output to be something like...
"Bob" "2019-02-07" "a"
"Jim" "2019-02-03" "b"
"Bob" "2019-02-10" "b"
"Frank" "2019-02-08" "b"
so i can can get max where create easy enough but how should i exclude the ones that have been deleted after creation?
this gets me close... but i need to remove the ones with a later deleted row.. SELECT max(tDate), tname, tType FROM test1 WHERE tAction = 'create' GROUP BY tname, ttype
thanks for any help! j.