How to do this JOIN

Question

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 Hogan 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.

Is 'User1' a string you want to define, or is it found in the table somewhere? Can you show some sample data and desired results? It's much easier than everyone reverse engineering your word problem... — Aaron Bertrand, Aug 08 '11 at 00:48

Phil · Accepted Answer · 2011-08-08T01:49:19.957

2

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

edited Aug 08 '11 at 01:49

answered Aug 08 '11 at 00:53

Phil

157,677
23
242
245

I guess you meant `OUTER JOIN`, because `INNER` will give only rows where there is match for both `From` and `To`. – Alexander Malakhov Aug 08 '11 at 01:10
@Alexander I kind of figured there had to be both a "From" and "To" reference (typical phone call scenario) so `INNER JOIN` is the correct type – Phil Aug 08 '11 at 01:24
Would this force both [From] and [To] to match in order for the row to be selected? I didn't know you could JOIN the same table twice, thanks :) – CodeMinion Aug 08 '11 at 01:28
@Kitler Both `[From]` and `[To]` would have to match phone numbers in your `Resource` table for a record to be retrieved. If there's a possibility of no match for one or both yet you still want a record returned, use `LEFT` instead of `INNER` joins – Phil Aug 08 '11 at 01:36
Ah, I mentioned something in the previous question that I forgot to add here. There are two tables I need to join to, [Resource] contains a Username and ID pair while [ResourcePhone] contains a Phone number and ID pair. So I need a username out of [Resource] but need to find a matching ID in [ResourcePhone] first. – CodeMinion Aug 08 '11 at 01:43
@Kitler I've updated my answer. You can keep joining in tables as many times as you need though it might be neater to create a "ResourcePhoneUser" view – Phil Aug 08 '11 at 01:50

score 1 · Answer 2 · answered Aug 08 '11 at 01:55

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

I'm not familiar with `CROSS APPLY` but are they actual sub-queries, performed for each row in `CallDetails`? How does this compare performance-wise to straight joins where there is, in all likelihood, foreign key references and proper indexing? — Phil, Aug 08 '11 at 02:03
Sorry Siva, I can only pick one answer :( this is interesting though, I'm trying out your answer to see how it works, although I am getting an error I haven't seen before! : "Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in the equal to operation." — CodeMinion, Aug 08 '11 at 02:09
Your guess is right, one of my data sources sits on a sql server 2005 instance, so only one of them is 2008 R2 — CodeMinion, Aug 08 '11 at 02:16

How to do this JOIN

2 Answers2