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

user_17643919 0
Viewed 37

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...

xampp mysql sql php
1 Answers

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

user_164619520
Related