Common records from SQL table

Question

I have 3 tables called CompanyInfo , IDInfo and PersonalInfo.

The objective is to fetch P.[First Name], P.[Last Name], I.PAN, C.DOB.

I can put left join or right join to cater this but the sequence of tables may change as it is an input to some tool and user may enter the table names in any sequence like user1 mentions "CompanyInfo, IDInfo, PersonalInfo", user2 mentions "IDInfo, PersonalInfo, CompanyInfo" and so on.

Table data is as below, Is there a way I can fetch the data through single SQL query (may be union). Say for if I want to fetch the data for ID = 4, I should get:

FirstName LastName  PAN        DOB
User      Four      UAN44444   NULL

If ID = 3

FirstName LastName  PAN        DOB
User      THree     NULL       1987-12-08

CompanyInfo Table

ID  Company    OfficialEmail        EmpID     DOB          Department   Joining Date  Status
1   RBS        userone@rbs.co.uk    UK222222  1980-11-15   HR           2012-11-20    Inactive
3   Infosys    userthree@infy.com   IN333333  1987-12-08   Admin        2016-08-18    Inactive
5   IBM        userfive@us.ibm.com  US55555   1986-03-26   Finance      2014-06-26    Active
10  Samsung    userten@samsung.com  SK101010  1988-04-04   Admin        2013-04-07    Active

IDInfo Table

ID  UAN         TIN         PAN
2   UAN22222    TIN222222   PAN22222
4   UAN44444    TIN444444   PAN44444
5   UAN55555    TIN555555   PAN55555

PersonalInfo Table

ID  FirstName   LastName    PhoneNumber      City         Address            ZipCode    Email
1   User        One         +44-7432564125   London       United Kingdom     RG231FDT   userone@gmail.com
2   User        Two         +91-987654321    New Delhi    India              110006  usertwo@gmail.com
3   User        Three       +44-782136425    Guildford    United Kingdom     GUI74DS    userthree@gmail.com
4   User        Four        +1-230156428     Atlanta      United States      GA 30337   userfour@gmail.com
5   User        Five        +1-650324152     Houston      United States      TX 77077   userfive@gmail.com
6   User        Six         +91-8885552223   Mumbai       India              400012  usersix@gmail.com
7   User        Seven       +91-9998887771   Bangalore    India              560021  userseven@gmail.com

Try my answer. Hope it helps you. – DineshDB Nov 28 '19 at 04:37 — DineshDB, Nov 28 '19 at 04:37

score 0 · Answer 1 · edited Nov 28 '19 at 04:54

0

Simple LEFT JOIN will resolve your problem.

SELECT P.FirstName, P.LastName, I.PAN, C.DOB 
FROM PersonalInfo P
LEFT JOIN IDInfo I  ON I.ID = P.ID
LEFT JOIN CompanyInfo C ON C.ID = P.ID
WHERE P.ID = @YourInput

edited Nov 28 '19 at 04:54

Dale K

25,246
15
42
71

answered Nov 28 '19 at 04:36

DineshDB

5,998
7
33
49

score 0 · Answer 2 · edited Nov 28 '19 at 06:59

0

You can use left join and right join:

SELECT P.FirstName, P.LastName, I.PAN, C.DOB. FROM IDInfo i LEFT JOIN PersonalInfo p ON p.ID = i.ID LEFT JOIN CompanyInfo c ON c.ID = i.ID WHERE i.ID = 3

edited Nov 28 '19 at 06:59

Pingolin

3,161
6
25
40

answered Nov 28 '19 at 06:46

Swetha Sekar

237
1
9

score 0 · Answer 3 · answered Nov 29 '19 at 07:21

Creating table variables to hold data of 3 table

DECLARE @CompanyInfo TABLE 
(ID INT,Company NVARCHAR(50),OfficialEmail NVARCHAR(50),EmpID NVARCHAR(50),DOB NVARCHAR(50),Department NVARCHAR(50),[Joining Date] NVARCHAR(50),[Status] NVARCHAR(50))

DECLARE @IDInfo TABLE
(ID INT,UAN NVARCHAR(50),TIN NVARCHAR(50),PAN NVARCHAR(50))

DECLARE @PersonalInfo TABLE
(ID INT,FirstName NVARCHAR(50),LastName NVARCHAR(50),PhoneNumber NVARCHAR(50),City NVARCHAR(50),[Address] NVARCHAR(50),ZipCode NVARCHAR(50),Email NVARCHAR(50))

Populate data to the tables

INSERT INTO @CompanyInfo VALUES
(1,'RBS','userone@rbs.co.uk','UK222222','1980-11-15','HR','2012-11-20','Inactive'),
(3,'Infosys','userthree@infy.com','IN333333','1987-12-08','Admin','2016-08-18','Inactive'),
(5,'IBM','userfive@us.ibm.com','US55555','1986-03-26','Finance','2014-06-26','Active'),
(10,'Samsung','userten@samsung.com','SK101010','1988-04-04','Admin','2013-04-07','Active')

INSERT INTO @IDInfo VALUES
(2,'UAN22222','TIN222222','PAN22222'),
(4,'UAN44444','TIN444444','PAN44444'),
(5,'UAN55555','TIN555555','PAN55555')

INSERT INTO @PersonalInfo VALUES
(1,'User','One','+44-7432564125','London','United Kingdom','RG231FDT','userone@gmail.com'),
(2,'User','Two','+91-987654321','New Delhi','India','110006','usertwo@gmail.com'),
(3,'User','Three','+44-782136425','Guildford','United Kingdom','GUI74DS','userthree@gmail.com'),
(4,'User','Four','+1-230156428','Atlanta','United States','GA 30337','userfour@gmail.com'),
(5,'User','Five','+1-650324152','Houston','United States','TX 77077','userfive@gmail.com'),
(6,'User','Six','+91-8885552223','Mumbai','India','400012','usersix@gmail.com'),
(7,'User','Seven','+91-9998887771','Bangalore','India','560021','userseven@gmail.com')

Query to obtain the required output

SELECT 
    P.FirstName, 
    P.LastName, 
    I.PAN, 
    C.DOB 
FROM 
    @PersonalInfo P LEFT JOIN @IDInfo I  ON I.ID = P.ID
    LEFT JOIN @CompanyInfo C ON C.ID = P.ID
WHERE 
    P.ID = 4

OUTPUT :-

FirstName   LastName    PAN         DOB
User        Four        PAN44444    NULL

Common records from SQL table

3 Answers3