Returning one row

Posted on 2011-10-19
Last Modified: 2012-05-12
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 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

Question by:Camillia
    LVL 39

    Accepted 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.
    LVL 7

    Author Comment

    let me see, i will post back

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
    So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now