Return 2 fields from same table

I have a 2 tables and in one of them...i have 2 IDs that refer to the first table. I need to join and get the "first name", "last name". Not sure how to do this...need a pivot something?

sample table/data below and what I'm trying...

It returns

Linda Jones  Bob Jones  Bob Jones
But it should return

Linda Jones  Bob Jones xxx yyy
---- sample table / data
create table #SignUpHCprovider
(
  id int,
  BusinessNameId int,
  FirstName varchar(50),
  lastname varchar(50)
)

Create table #IncomingPatient
(
  BusinessNameId int,
  firstname varchar(50),
  lastname varchar(50),
  HCProviderId int,
  ReferralProviderID int
  
)

insert into #SignUpHCprovider
  select 1, 3, 'bob','jones'
 
insert into #SignUpHCprovider
  select 11,3, 'jane','doe'

insert into #SignUpHCprovider
  select 44,8,'ppp','mmm'

insert into #SignUpHCprovider
  select 66,10,'xxx','yyy'

insert into #IncomingPatient
  select 3, 'Linda','Jones',1, 66 

--- my sql

SELECT
 
 ip.FirstName + '' + ip.lastname as PatientName,
 sp.FirstName + ' ' + sp.LastName as ReferredTo,

 sp.FirstName + ' ' + sp.LastName as ReferredFrom
 from #IncomingPatient ip
       left join #SignupHCProvider sp on sp.Id  = ip.HCProviderId
       left join #SignupHCProvider sp1 on sp1.Id  = sp.id and ip.ReferralProviderID = sp.id -- this is wrong

Open in new window

LVL 8
CamilliaAsked:
Who is Participating?
 
JestersGrindConnect With a Mentor Commented:
Try the SQL query below.

Greg


SELECT 
 
 ip.FirstName + '' + ip.lastname as PatientName,
 sp.FirstName + ' ' + sp.LastName as ReferredTo,
 sp1.FirstName + ' ' + sp1.LastName as ReferredFrom
 from #IncomingPatient ip
       left join #SignupHCProvider sp on sp.Id  = ip.HCProviderId
       left join #SignupHCProvider sp1 on ip.ReferralProviderID = sp1.id

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about:
from #IncomingPatient ip
       left join #SignupHCProvider sp on sp.Id  = ip.HCProviderId
       left join #SignupHCProvider sp1 on sp1.id = ip.ReferralProviderID

Open in new window

0
 
CamilliaAuthor Commented:
no, i had tried that. Same result...

LindaJones      bob jones      bob jones
should be
LindaJones      bob jones      xxx yyy
0
 
CamilliaAuthor Commented:
yes, that worked, JestersGrind: ( I didnt see your post earlier)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.