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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 134
  • Last Modified:

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

0
Camillia
Asked:
Camillia
  • 2
1 Solution
 
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
 
JestersGrindCommented:
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
 
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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