How to create VIEW witch combined and filtraded result from multiple rows-CodePudding

Hi i have problem with task i need to do. I have a table in SQL showing log of users (subscibers) with triggers that add TIMESTAMP, ACTION PERFORMED and NAME of the subscriber, looks something like this:

TABLE: audit_subscibers

ID	Name	Action	Time
0	John	Insert a subscriber	2020-1-1
1	John	Deleted a subscriber	2020-3-1
2	Mark	Insert a subscriber	2020-4-5
3	Andrew	Insert a subscriber	2020-5-1
4	Andrew	Updated a subscriber	2020-5-15

Now i need to create a VIEW that shows ONLY subscribers (name) that has been deleted with DELETE TIME and INSERTION TIME, to achieve something like this:

'John' was added (like every other member) but also has beed deleted, not touching members that has only INSERTED

Name	Date added	Date deleted
John	2020-1-1	2020-3-1
some other	2020-x-x	2020-y-y

How to achieve this , taking only subscibers that has >1 entries and also one entry must indicate that user has been DELETED :Deleted a subscriber , and combine RESULT i ONE ROW?

I have another similar task, but this time i must create view (based only on audit_subscibers table) i must show ONLY subscribers that STILL exist (take all subscrb. with "Insert a subscriber" but reject from result those who has more rows including "Deleted a subscriber"

I am relly aprecciate the answers...

CodePudding user response：

Maybe this can give you a hint or help:

CREATE TABLE  audit_subscibers (
 id int ,
 name varchar(30),
 action varchar(60),
 time date );

INSERT INTO audit_subscibers VALUES
(0,'John','Insert a subscriber','2020-01-01'),
(1,'John','Deleted a subscriber','2020-03-01'),
(2,'Mark','Insert a subscriber','2020-04-05'),
(3,'Andrew','Insert a subscriber','2020-05-01'),
(4,'Andrew','Updated a subscriber','2020-05-15');




SELECT name,
       MAX(case when action='Insert a subscriber' then time end) as Date_added,
       MAX(case when action='Deleted a subscriber' then time end) as Date_deleted
FROM    (
SELECT name,time,action
FROM audit_subscibers
WHERE name in (SELECT name  
               FROM audit_subscibers  
               WHERE action in ('Insert a subscriber','Deleted a subscriber') 
               GROUP BY name
               HAVING COUNT(action) = 2 )

) as t1
group by name;

Result:

name  Date_added  Date_deleted
John  2020-01-01  2020-03-01

Demo https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ef8a766a516951166161419a75e49cc6

CodePudding user response：

You have to add one more column (like delete_time) in table where your records has saved/updated. Your action has saved under Action column and while performing delete record then you will update the same column (delete_time).

Thanks