Records on the basis of two different tables.

Question

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.

Answer 1

That is not really a clear description of what you want.  Also, if you want to help someone help you, you should provide a script to create the tables and insert some sample data.  See: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Without better understanding it seems that you are asking for a Full Outer Join.  E.g.

SELECT COALESCE(a.Id,b.Id) AS Id, 
       a.Name AS Emp_Column, b.Name AS EmpCopy_Column
FROM Emp AS a  
     FULL OUTER JOIN EmpCopy as b
		ON a.Id = b.Id
   

FWIW, If you are trying to compare table definitions between two tables, Red Gate, Apex, and others make tools to help with that.

RLF