Home > DeveloperSection > Forums > Retreiveing records on the basis of two different tables.
Chris Anderson
Chris Anderson

Total Post:65

Points:455
Posted on    August-03-2012 8:01 AM

 MSSQL Server MSSQL Server 
Ratings:


 2 Reply(s)
 1035  View(s)
Rate this:

Suppose I have two tables (Emp and EmpCopy) in sql server database with following data:

Case 1:  Data in Emp and EmpCopy  table present like below:

Table:  Emp

Id            Name

----------------------

1              Emp_Name

2              Emp_Age

3               Emp_City

Table: EmpCopy

Id            Name

--------------------

1              Emp_Name

2              Emp_Age

Case 2: Data in Emp and EmpCopy table present like below:

Table:  Emp

Id            Name

--------------------

1              Emp_Name

2              Emp_Age

Table: EmpCopy

Id            Name

---------------------

1              Emp_Name

2               Emp_Age

3               Emp_City

Now according to cases given above I want retrieve records from both the tables, just like given below

Case 1:

Id            Emp_Column    EmpCopy_Column

1              Emp_Name        Emp_Name

2              Emp_Age            Emp_Age

3              Emp_City             NULL

Case 2:

Id            Emp_Column    EmpCopy_Column

1              Emp_Name        Emp_Name

2              Emp_Age            Emp_Age

3              NULL                     Emp_City

Please reply ASAP.

Thanks in advance.



Shankar M

Total Post:25

Points:175
Posted on    April-09-2013 9:53 PM

Hi Rohit,

Considering your First Case, Here is the Query

SELECT A.ID,A.NAME,B.NAME FROM EMP A LEFT OUTER JOIN EMPCOPY B ON A.ID = B.ID AND A.NAME =B.NAME

And the Second case which is a little reverse of First One,

So I have used the Same Tables but changing the Type of Join 

SELECT B.ID,A.NAME,B.NAME FROM EMPCOPY A RIGHT OUTER JOIN EMP B ON A.ID = B.ID  AND A.NAME =B.NAME

Ideally, If you consider only the ID Column in both tables then, the query turns out to be

First Case,
SELECT A.ID,A.NAME,B.NAME FROM EMP A LEFT OUTER JOIN EMPCOPY B ON A.ID = B.ID

Second Case,

SELECT B.ID,A.NAME,B.NAME FROM EMPCOPY A RIGHT OUTER JOIN EMP B ON A.ID = B.ID 

Thanks,
Shankar



Chris Anderson
Chris Anderson

Total Post:65

Points:455
Posted on    April-18-2013 4:49 AM

thanks shankar.

Don't want to miss updates? Please click the below button!

Follow MindStick