RETREIVEING RECORDS ON THE BASIS OF TWO DIFFERENT TABLES.

Chris Anderson

Total Post:65

Points:455
Posted by  Chris Anderson
 1236  View(s)
Ratings:
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.

  1. Shankar M

    Post:25

    Points:175
    Re: Retreiveing records on the basis of two different tables.

    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


  1. Chris Anderson

    Post:65

    Points:455
    Re: Retreiveing records on the basis of two different tables.

    thanks shankar.

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!