I got two tables with email addresses and a third table with a foreign key relation to the user table and I wish to get some distinct email addresses back. Here's a representation of the data:
Table 1 (user)
| Id | Email |
| -------- | -------------- |
| 1 | [email protected] |
| 2 | [email protected] |
| 3 | [email protected] |
Table 2 - foreign key to user table
| Id | user_id |
| -------- | -------------- |
| 19 | 1 |
| 45 | 2 |
Table 3 (no relationship to the other two tables)
| Id | email |
| -------- | -----------------|
| 12 | [email protected] |
| 39 | [email protected] |
How can I get all distinct email addresses from table 3 and table 1 where email does not exist in table 2 (fetched through user_id.email)
So in the scenario above, I'd like to get back:
[email protected]
[email protected]
and exclude [email protected] and [email protected].
CodePudding user response：
I believe what you want to do is a union between emails from Table 1 and Table 3, except emails of users from Table1 appearing in Table2:
(select "Email" from table1
union select "email" from table3)
except
(select "Email" from Table1 inner join Table2
on Table1."Id" = Table2."user_id")