Search code examples
sqlt-sqlms-access

How can I solve this question according to TSQL syntax?

I was studying for a test until this question appeared

These are the tables (primary keys are in bold):

  • Employee (id, name, emailAddress),
  • Email (emailId, senderId, receiverId, subject)
  • EmailDetails (emailId, date, status)

The question states that:

  • Two employees are connected if they sent an email to each other
    • (i.e. each employee should have at least sent the other one or more emails).
  • Find all pairs of employees who are connected.
    • (return their ID).

Can someone please help me solving this out?

Solution

  • You can check only the email table, if an id is sender and receiver

    SELECT DISTINCT senderID,receiverID  FROM Email e 
WHERE EXISTS (SELECT 1 
              FROM Email  
              WHERE receiverID = e.senderID AND senderIF = e.receiverID))