Help with complex SQL query (a lot of JOINs?)

Question

There are two databases and 4 tables involved in this query. The first database(PhoneBills) contains the first table(CallDetails), and within this there are columns for :

1. [Time] (start time)
2. [From] (caller number)
3. [To] (target number)
4. [Cost] (in money)
5. [Length] (how long called)

The columns of interest here are [From] and [To].

In the second database(rtc - this is a Lync persistent data DB) there are three tables of interest :

1. Resource (matches ResourceID to Username)
2. ResourceDirectory (defines the Time a ResourceID was Inserted and when it was last Updated)
3. ResourcePhone (matches a ResouceID to a phonenumber)

I don't honestly know what ResourceDirectory defines, I am guessing that the phone numbers that usernames are matched to can change, and the directory keeps track of those times. For the purposes of simplification, I will ignore this part for now.

What I am trying to achieve is to get the Username from Resource given that I can find a match for my phone number in ResourcePhone.

Answer 1

Not tested. I didn't have coffee this morning. You've been warned.

select 
  pb.Time, 
  pb.From, r1.Username as FromName, 
  pb.To,   r2.Username as ToName, 
  pb.Cost, pb.Length
from PhoneBills pb
inner join ResourcePhone rpfr on rpfr.PhoneNumber = pb.From
inner join ResourcePhone rpto on rpto.PhoneNumber = pb.To
inner join Resource r1 on r1.ResourceID = rpfr.ResourceID
inner join Resource r2 on r2.ResourceID = rpto.ResourceID

Join PhoneBills to ResourcePhone to get access to the ResourceID for a specific phone number. Then join that ResourceID to the table Resource to get access to the username.

Answer 2

Won't this work?

SELECT Username
FROM Resource R
JOIN ResourcePhone RP on R.ResourceID = RP.ResourceID
WHERE RP.phonenumber = '1111111111'