Need to join three tables and find whats missing

user_18964303 0
Viewed 53

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?

mysql
3 Answers
user_142547710

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;
user_167118610 
SELECT
    cw_Contact.Contact_RecID,
    cw_Contact.Company_RecID,
    cw_Contact.First_Name,
    cw_Contact.Last_Name, 
    cw_Member.Member_RecID
FROM
    cw_Contact
    JOIN users ON users.Support_Team_Long = cw_Contact.Support_Team
    
WHERE
    NOT EXISTS ( SELECT 1 FROM notification_contacts WHERE notification_contacts.Contact_RecID = cw_Contact.Contact_Recid AND notification_contacts.Member_RecID = users.SR_Member_RecID )
user_189643030
Related