CODEWITHSUNDEEP
sqlsql-server-2008
GPH
GPH

Reputation: 1897

SQL join on multiple Rows to Single row

i am struggling to do a join in the way that I need, I have two tables that house data I need to link(just to be clear). One table holds Outbound Call data, number dialed, duration ect. the other table holds contact details of the people dialed. Set out as below:

CustID | Number 1  | Number 2 | Number 3  | Number 4
    1  | 072454584 |          | 017726593 | 
    2  |           |0125456852|           | 0125785448

So if we wanted to call Customer 1 we would try both numbers, with only one connecting.

What i need to do is join the number dialed to the customer records but so that it compares each number till it matches match (hoping this makes sense). I've tried a case when Statement but it didn't work. Whats the best approach in doing this?!?!

Upvotes: 2

Views: 128

Answers (2)

Dmitri M
Dmitri M

Reputation: 1166

I would probably take this approach to the query.

with myphones
AS
(
    SELECT CustomerId, Phone1 As Phone FROM ContactDetails
    UNION
    SELECT CustomerId, Phone2 As Phone FROM ContactDetails
    UNION
    SELECT CustomerId, Phone3 As Phone FROM ContactDetails
    UNION
    SELECT CustomerId, Phone4 As Phone FROM ContactDetails
)
SELECT p.CustomerId, p.Phone, oc.*
FROM myphones p 
    INNER JOIN outboundcalls oc ON p.Phone = oc.Phone

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You want to use a series of left outer join's with a conditional statement on the match:

select cd.CustId,
       coalesce(oc1.number, oc2.number, oc3.number, oc4.number) as MatchingNumber,
       (case when oc1.Number is not null then 'Number1'
             when oc2.Number is not null then 'Number2'
             when oc3.Number is not null then 'Number3'
             when oc4.Number is not null then 'Number4'
        end) as WhichMatch
from ContactDetails cd left outer join
     OutboundCalls oc1
     on cd.number1 = oc1.number left outer join
     OutboundCalls oc2
     on cd.number2 = oc2.number left outer join
     OutboundCalls oc3
     on cd.number3 = oc3.number left outer join
     OutboundCalls oc4
     on cd.number4 = oc4.number;

The left outer join attempts to match to each number in the list. The coalesce() will choose the first matching number, and the case tells you which number matches.

Note that if you have multiple successful outbound calls for a given customer, you will get multiple rows in the output.

Upvotes: 2

Related Questions