Use of self join in SQL

Suppose we have an Employee table. All the employees  and their supervisor (or Manager) are in the same table. If we want to fetch the employees and their supervisors, how we could get them?

For that you need to choose self join. Self join is an inner join but only applied with one table. For applying self join we need 2 instance of the same table in memory. We have to use alias for making identical copies of the table because there is only one table.

See the example below.
Lets create a employee table first.

CREATE table mtblEmployee
(Employee_Id int identity (1,1),
Employee_Name nvarchar(50),
Supervisor_Id int
)

Now insert some value in this table

INSERT INTO mtblEmployee
SELECT 'Amit Mittal',0
UNION ALL 
SELECT 'Piyush Sharma',3
UNION ALL 
SELECT 'Dinesh Aggarwal',6
UNION ALL
SELECT 'Shobhit Roy',5
UNION ALL
SELECT 'Varun Dhiman',1

fetch the records

select * from mtblEmployee

By using self join we can fetch the employee and their supervisors

The below both query will give the same result

SELECT a.Employee_Id as Id, a.Employee_Name as Employee,
b.Employee_Name as Supervisor
FROM mtblEmployee a 
INNER JOIN  mtblEmployee b
on a.Supervisor_Id=b.Employee_Id

OR

SELECT a.Employee_Id as Id, a.Employee_Name as Employee,
b.Employee_Name as Supervisor 
FROM mtblEmployee a, mtblEmployee b 
where a.Supervisor_Id=b.Employee_Id

Result:

I hope it would help.

Thanks

SQL Query to Find Unsaved Records

Hi guys,

            I have 2 sql tables. 1st one is main table and the  2nd one is for backup.
            Suppose if you get a situation, like you need to find out the records which are not backed up yet, then here I am posting a method to find out the remaining records.

Here I am creating 2 tables and inserting some values.

 CREATE TABLE Table1  
   ([SNo] int, [Item] varchar(50))  
   ;  
 INSERT INTO Table1  
   ([SNo], [Item])  
 VALUES  
   (1, 'A'),  
   (2, 'B'),  
   (3, 'C'),  
   (4, 'D'),  
   (5, 'E');  
 CREATE TABLE Table2  
   ([SNo] int, [Item] varchar(50));  
 INSERT INTO Table2  
   ([SNo], [Item])  
 VALUES  
   (1, 'A'),  
   (2, 'B'),  
   (3, 'C');  

You can get those records by using sub query like below.

 SELECT * FROM Table1 WHERE Sno NOT IN (Select SNo FROM Table2)  

 Result :

| SNo | Item |
|-----|------|
|   4 |    D |
|   5 |    E | 

You can also get the solution by using below query 

 select * from Table1 except select * from Table2  

Result : 

| SNo | Item |
|-----|------|
|   4 |    D |
|   5 |    E | 

I hope it would be helpful. 

Thanks 

Method Overloading and Overridding

Overloading and Overriding are the parts of Polymorphism.

Overloading is compile time polymorphism, also known as Early Binding or Static Binding.

Overriding is  run-time polymorphism, also known as Late Binding or Dynamic Binding.

So first we'll go through the Overloading.

Suppose you  need to define a method, but you don't have any idea about how many parameters would be there and also don't know the what type of data it would be.

Let's take a method named Insert_Employee.

 class Insert_Employee_Data  
   {  
     public static void Insert_Employee(int EmployeeID)  
     {  
       // Inserts EmployeeID as given and Employee's FirstName and LastName as empty  
     }  
     public static void Insert_Employee(int EmployeeID, string FirstName)  
     {  
       // Inserts EmployeeID and Employee Name as given whereas, LastName as empty  
     }  
     public static void Insert_Employee(int EmployeeID, string FirstName, string LastName)  
     {  
       // Inserts EmployeeID, Employee's FisrtName and LastName as given  
     }  

When your class's behaviours have totally different implementation but intended for single target then use Overloading.


Now let's go through Overriding.

Take a situation where you need to  use the base class's method in derived class with some add-on.
Suppose you have a price calculation class, which calculates the price and returns it to you.

The price calculation method should be declared as virtual, so that enhancements can be done to it later on (adding some expense may be).

   class Price_Calculation  
   {  
     public decimal UnitPrice { get; set; }  
     private int Quantity { get; set; }  
   
     public virtual decimal CalculatePrice()  
     {  
       return (UnitPrice * Quantity);  
     }  
   }  
   /// <summary>  
   /// This class also calculates the Price Amount but returns the total amount with Expenses also  
   /// </summary>  
   class Price_And_Expense_Calculation : PriceCalculation  
   {  
     public decimal ExpenseAmount { get; set; }  

     /// This method does not calculate the price itself, but overrideds the price calculation method and just  
     /// adds the expense amount to it  
   
     public override decimal CalculatePrice()  
     {  
       return (base.CalculatePrice() + ExpenseAmount);  
     }  
   }  

I hope you got the point to use overriding, means, if your class's behaviors have anything is common and can be shared with other derived forms of classes then they should be overidden.