How can I solve this question according to TSQL syntax?-CodePudding

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?

CodePudding user response：

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))

CodePudding user response：

This will give you all pairs, so (bob, kate) and (kate, bob).

select distinct e1.senderID, e1.receiverID
from email e1 join email e2
on e1.receiverID = e2.senderID and e2.receiverID = e1.senderID;

To get only one of those add at the end a condition:

where e1.senderId > e1.receiverID

CodePudding user response：

To be able to correctly answer this question you need to mark which columns in the given tables serve as foreign keys, i.e. their values correspond to each other. Are senderid and receiverid id in the employees table? if so:

select emp1.id as sender, emp2.id as receiver from employee emp1 join email e on emp1.id = e.senderid join employee emp2 on emp2.id = e.receiverid