CODEWITHSUNDEEP
sqlsql-serversql-server-2008join
Billa
Billa

Reputation: 5266

JOINING Same Table gives duplicate information

I want to join the same table to get some information.

I have a Person Table 

PersonId, FirstName, LastName, Address

Also Patient Table

PatientId, PersonId, ResponsiblePersonId

Below is the query i tried to get Patient's FirstName, Last Name also the Responsible Person FirstName, Last Name. For this I JOINED Person table once again to get Responsible Persons First, Last names.

But I got many duplicate records.

SELECT   PAT.PatientId

        ,PER.PersonNumber
        ,PER.FirstName
        ,PER.LastName
        ,RES_PER.FirstName AS ResFirstName
        ,RES_PER.LastName AS ResLastName
        ,PER.Address                

FROM        dbo.Patient AS PAT
INNER JOIN  dbo.Person AS PER
    ON  PAT.PersonId = PER.PersonId
INNER JOIN  dbo.Person AS RES_PER
    ON  PAT.ResponsiblePersonId = PER.PersonId

How can i get Patient FirstName, LastName & Responsible Person's First Name, Last Name for the patient record?

Upvotes: 1

Views: 2203

Answers (2)

xagyg
xagyg

Reputation: 9721

All you need to do is replace PER with RES_PER in the very last bit of your SQL. (i.e. PER.PersonId becomes RES_PER.PersonId).

SELECT   PAT.PatientId
        ,PER.PersonNumber
        ,PER.FirstName
        ,PER.LastName
        ,RES_PER.FirstName AS ResFirstName
        ,RES_PER.LastName AS ResLastName
        ,PER.Address                

FROM        dbo.Patient AS PAT
INNER JOIN  dbo.Person AS PER
    ON  PAT.PersonId = PER.PersonId
INNER JOIN  dbo.Person AS RES_PER
    ON  PAT.ResponsiblePersonId = RES_PER.PersonId

Upvotes: 1

John Woo
John Woo

Reputation: 263893

Basically, you need to join table Person twice on table Patient in order to get the two dependent columns on it.

SELECT  b.FirstName Patient_FirstName, 
        b.LastName Patient_LastName,  
        b.Address Patient_Address,
        c.FirstName Responsible_Firstname, 
        c.LastName Responsible_LastName, 
        c.Address Responsible_Address
FROM    Patient a
        INNER JOIN Person b
            ON a.PersonID = b.PersonID
        INNER JOIN Person c
            ON a.ResponsiblePersonId = c.PersonID

To further gain more knowledge about joins, kindly visit the link below:

Upvotes: 1

Related Questions