Sqlserver: Retrieve more than one reference columns of same table - using Join table twice or Sub Query.

Introduction :

Many times, we have put in a situation like we have one table which have more than one reference key column which refer to same table. Now when we select records from that table it will little bit confusing. So here, I have described two methods for select records with example.

Implementation :

I have Country SqlServer table as defined below:

Country table contains following data:

Country table used reference with Student table which is defined below:

Student table contains following data:

From above screens, we have observed that ResidentialCountryID and CommunicationCountryID Columns are reference keys of Country Table. It was little bit confusing how to retrieve StudentResidentialCountryName and CommunicationCountryName which are referring same table. We can do this using following two methods.

Using Join Table twice :

To Retrieve StudentResidentialCountryName and CommunicationCountryName from Country table we need to join Country table twice with Student table with different alias name.

 Select s.StudentName,s.ResidentialAddress,s.ResidentialCountryID,rc.Name as   'ResidentialCountry',s.CommunicationAddress,s.CommunicationCountryID,cc.Name as 'CommunicationCounty' 
From Student s
Inner join Country cc on s.CommunicationCountryID=cc.Id
Inner join Country rc on s.ResidentialCountryID=rc.Id

In above Query, we can see that Country table join twice with alias of cc and rc.Execute above query it will display following students table result with country name.

Using Sub Query :

To Retrieve StudentResidentialCountryName and CommunicationCountryName from Country table we can also use sub query with student table.

Select s.StudentName,s.ResidentialAddress,s.ResidentialCountryID,
(Select Name From Country Where Id=s.ResidentialCountryID)as 'ResidentialCountry',
s.CommunicationAddress,
s.CommunicationCountryID,
(Select Name From Country Where Id=s.CommunicationCountryID)as 'CommunicationCounty' 
From Student s

In above query, we have used inline sub query for retrieve Residential Country and Communication Country. The result of the above query is display below.

Conclusion:

The goal of this article is just wants to show you that we can retrieve data in two ways when one table contains more than one reference column of the same table. Hope, this will help you.