Returning one row

I have a table SignupHCProvider that lists all provider names with their Id.



I have another table like this: This is IncomingPatient which has a HCProviderId (patient is referred to this doctor) and ReferralProvideId (the dcotor who referred this patient). Both link back to SignupHCProvider table


I want to join to SignUpHCProvider table and get firstname, lastname for HCProviderId and eferralProviderId, so I did this ( i have other fields
  but removed them)

My select gives me 2 rows..it should give me one row:

patientname  ReferredTo ReferredFrom
Linda Jones  Bob Jones   Jane Doe

sample data and sql below

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.BusinessNameId  = ip.BusinessNameId

----



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

Select allOpen in new window