I have these two tables. Subscriber table contains SubscriberID and his Phone number. NetworkP2P contents SubscriberID, to who he has called AddresseeNumber and when the call started, ended.
TABLE Subscriber
(
SubscriberID int,
PhoneNumber char(10)
)
TABLE NetworkP2P
(
SubscriberID int,
AddresseeNumber char(10),
CallStart datetime,
CallEnd datetime
)
Example data:
Subscriber
SubscriberID | PhoneNumber
1 613-555-0156
2 613-555-0112
3 613-555-0119
4 613-555-0182
NetworkP2P
SubscriberID | AddresseeNumber | CallStart | CallEnd
1 613-555-0182 2013-02-03 08:27:00.000 2013-02-03 08:30:54.000
1 613-555-0119 2013-02-03 10:11:26.000 2013-02-03 10:14:26.000
1 613-555-3333 2013-02-03 15:13:58.000 2013-02-03 15:18:13.000
2 613-555-0156 2013-02-05 23:21:50.000 2013-02-05 23:24:50.000
2 613-555-0119 2013-04-05 23:21:50.000 2013-04-05 23:26:50.000
3 613-555-0112 2013-06-05 23:21:50.000 2013-06-05 23:26:50.000
Not all Phone numbers match the ones that Subscribers have.
How would I approach this, if I wish to have which outputs subscribers who have been in contact with each other for a given month, i.e. subscriber1 called subscriber2 and vice versa?
Example of the desired output:
Caller | Reciever | CallerPhone | RecieverPhone
1 4 613-555-0156 613-555-0182
1 3 613-555-0156 613-555-0119
2 1 613-555-0112 613-555-0156
2 3 613-555-0112 613-555-0119
3 2 613-555-0119 613-555-0112
CodePudding user response:
Your model is not wrong as suggested in comments - your NetworkP2P might be missing only one column (Reciever), which could work as optional foreign key (to satisfy your sentence "Not all Phone numbers match the ones that Subscribers have."), but it looks more log than relational table.
Query for your desired output:
select
caller.SubscriberID as Caller,
reciever.SubscriberID as Reciever,
caller.PhoneNumber as CallerPhone,
nt.AddresseeNumber as RecieverPhone, -- better than reciever.PhoneNumber because you can use left join recievers to get missing substribers
nt.CallStart,
nt.CallEnd
from Subscriber as caller
inner join NetworkP2P as nt
on nt.SubscriberID = caller.SubscriberID
inner join Subscriber as reciever
on nt.AddresseeNumber = reciever.PhoneNumber
-- where '2021-02-03' between nt.CallStart and nt.CallEnd -- calls in 2021-02-03
-- where nt.CallStart between '2021-02-01' and '2021-02-28' -- calls in specific interval
-- where eomonth(nt.CallStart) = eomonth('2021-02-01') -- calls in specific month
You should always use database schema for all your tables (for example dbo.Subscriber).
CodePudding user response:
Two joins to Subscriber should do it.
SELECT nw.SubscriberID AS Caller , rcvr.SubscriberID AS Receiver , callr.PhoneNumber AS CallerPhone , nw.AddresseeNumber AS ReceiverPhone FROM NetworkP2P nw JOIN Subscriber callr ON callr.SubscriberID = nw.SubscriberID LEFT JOIN Subscriber rcvr ON rcvr.PhoneNumber = nw.AddresseeNumber WHERE rcvr.SubscriberID IS NOT NULL ORDER BY Caller, Receiver
Caller | Receiver | CallerPhone | ReceiverPhone |
---|---|---|---|
1 | 3 | 613-555-0156 | 613-555-0119 |
1 | 4 | 613-555-0156 | 613-555-0182 |
2 | 1 | 613-555-0112 | 613-555-0156 |
2 | 3 | 613-555-0112 | 613-555-0119 |
3 | 2 | 613-555-0119 | 613-555-0112 |
Demo on db<>fiddle here