Need to join three tables and find whats missing-CodePudding

I have three tables: Customers, Members, Communications

Customers
-------------
Customer_RecID |  First_Name | Last_Name
1              |  Bob        | Smith
2              |  James      | Jones
3              |  Chris      | Johnson


Members
--------------
Member_RecID  | First_Name | Last_Name
1             | Amy        | Kite
2             | Sara       | Macky
3             | Jill       | Brite
4             | New        | User


Communications
--------------
Communication_RecID | Customer_RecID | Member_RecID | Date
1                   | 1              | 1            | 2022-02-03 00:22:10
2                   | 1              | 2            | 2022-02-03 00:22:10
3                   | 1              | 3            | 2022-02-03 00:22:10
4                   | 2              | 1            | 2022-02-03 00:22:10
5                   | 2              | 2            | 2022-02-03 00:22:10
6                   | 2              | 3            | 2022-02-03 00:22:10
7                   | 3              | 1            | 2022-02-03 00:22:10
8                   | 3              | 2            | 2022-02-03 00:22:10
9                   | 3              | 4            | 2022-02-03 00:22:10

Communications holds all the times that Customer has contacted Member

What I need to find is a list of all the customers that have not contacted a member. So in my example, how do I find out which Customers have not contacted Members?

CodePudding user response：

You can achieve that by using subqueries and the NOT and IN operators.

CodePudding user response：

An alternative to using subqueries would be a left join with a where clause:

SELECT Customers.Customer_RecID
FROM Customers LEFT JOIN Communications ON Customers.Customer_RecID = Communications.Customer_RecID
WHERE Communications.Customer_RecID IS NULL;