MySQL , Exclude result set (from one view) from result set (from another view)

Question

Hi i have issue i cant solve, i have 2 views looking something like this:

Table 1(inserted users) ---------------------- Table 2(deleted users)

[Name] [Date]	[Name] [Date]
John ----	John ----
Andrew ----	Michael ----
Michael ----
Sam ----

And my task is to create a view based only of those 2 views, that contains only STILL EXISTING users, hence check if user with some NAME apper on the deleted table and exclude those from inserted users. I cant figure it out...

Answer 1

you can use a query like this:

SELECT NAME FROM table1 t1 WHERE NOT EXISTS (SELECT 1 FROM table2 t2 WHERE t1.name = t2.name);

sample

MariaDB [bernd]> select * from table1;
+----+-------+
| id | name  |
+----+-------+
|  1 | Peter |
|  2 | Paul  |
|  3 | John  |
+----+-------+
3 rows in set (0.02 sec)

MariaDB [bernd]> select * from table2;
+----+------+
| id | name |
+----+------+
|  1 | Paul |
+----+------+
1 row in set (0.00 sec)

MariaDB [bernd]> SELECT NAME FROM table1 t1 WHERE NOT EXISTS (SELECT 1 FROM table2 t2 WHERE t1.name = t2.name);
+-------+
| NAME  |
+-------+
| Peter |
| John  |
+-------+
2 rows in set (0.09 sec)

MariaDB [bernd]>