How to do this JOIN

Related to my previous post here, I have the following SELECT:

SELECT tc.[Time],tc.[From], tc.[To], tc.[Cost], tc.[Length], tc.[Type], tc.[PlaceCalled]    
FROM
TelstraCall as tc 
WHERE 
[AccountNumber] IN (@AccountNumber)
ORDER BY [Time] DESC

I'm trying to get the [Username] out of [Resource] given that the [PhoneNum] in [rtc] matches either [From] or [To], and 开发者_如何学PythonHogan has kindly helped me out with the first half :

USE [rtc]
SELECT [Username]
FROM [dbo].[Resource] R
JOIN ResourcePhone RP on R.ResourceId = RP.ResourceId
WHERE RP.PhoneNum = tc.[From]

Now I'm trying to work out the syntax of how to get a 'User1' given that [From] matches the [PhoneNum] in [rtc] and a 'User2' if [To] matches [PhoneNum] instead, because I can't have them being jumbled up.

What you're wanting to do is join on the same table twice to get related values based on two different references.

For this, you use table aliases. Here's a simple example

SELECT u1.[Username] AS User1, u2.[Username] AS User2
FROM TelstraCall tc
INNER JOIN ResourcePhone rp1 ON tc.[From] = rp1.PhoneNum
INNER JOIN Resource u1 ON rp1.ResourceId = u1.Id -- guessing at column names here
INNER JOIN ResourcePhone rp2 ON tc.[To] = rp2.PhoneNum
INNER JOIN Resource u2 ON rp2.ResourceId = u2.Id

Here is one way that you can do this using CROSS APPLY since you are using SQL Server 2008. CROSS APPLY helps you to join your table with sub queries.

In this case, the table CallDetails in the database PhoneBills drives your query using the fields From and To. Both these fields have to fetch the Username data from the table Resource in the database rtc by joining with the PhoneNumber column in the table ResourcePhone also in the database rtc.

So the inner/sub query will join the tables Resource and ResourcePhone, it will then be used twice to fetch User1 and User2. For User1, the filter will use the From field in the table CallDetails in the database PhoneBills and for User2, the filter will use the To field in the table CallDetails in the database PhoneBills

SELECT      USR1.UserName   AS [User1]
        ,   USR2.UserName   AS [User2]
FROM        PhoneBills.dbo.CallDetails  CD
CROSS APPLY (
                SELECT      Username
                FROM        rtc.dbo.Resource        R
                INNER JOIN  rtc.dbo.ResourcePhone   RP
                ON          RP.ResourceID           = R.ResourceID
                WHERE       RP.PhoneNumber          = CD.From               
            ) USR1
CROSS APPLY (
                SELECT      Username
                FROM        rtc.dbo.Resource        R
                INNER JOIN  rtc.dbo.ResourcePhone   RP
                ON          RP.ResourceID           = R.ResourceID
                WHERE       RP.PhoneNumber          = CD.To             
            ) USR2