[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 830
  • Last Modified:

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

Open in new window

1 Solution
You are joining on BusinessNameID which is not unique to SignupHCProvider.  You should store the Id from Signup, not the businessNameId unless you are to make it unique.
CamilliaAuthor Commented:
let me see, i will post back

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now