Self-join in sql with example

Sql equivalent queries in Linq
Set all check boxes inside a div 
Get number of weekend between two dates
Bulk insert in Sql 2008
Get nth highest salary in a table

Use of Temp Table and Split Function

For self join in sql you can try the following example:
Create table employees:

emp_id	emp_name	emp_manager_id
1	John	Null
2	Tom	1
3	Smith	1
4	Albert	2
5	David	2
6	Murphy	5
7	Petra	5

Now to get the names of managers from the above single table you can use sub queries or simply the self join.
Self Join SQL Query to get the names of manager and employees:
select e1.emp_name 'manager',e2.emp_name 'employee'
from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id
Result:

manager	employee
John	Tom
John	Smith
Tom	Albert
Tom	David
David	Murphy
David	Petra

In the above self join query, employees table is joined with itself using table aliases e1 and e2. This creates the two views of a single table.
from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id
Here e.emp_manager_id passes the manager id from the 2nd view to the first aliased e1 table to get the names of managers.

 Explain it with another example

 Let’s illustrate the need for a self join with an example. Suppose we have the following table called emp_location, with both the employee name and their location:

employee

employee_name employee_location Joe New York Sunil India Alex Russia Albert Canada Jack New York

Now, suppose we want to find out which employees are from the same location as the employee named Joe. What we could do is write a nested SQL query (basically a query within another query) like this:

SELECT employee_name FROM employee WHERE employee_location in ( SELECT employee_location FROM employee WHERE employee_name = "Joe")

A nested subquery for such a simple question is inefficient. Is there a more efficient and elegant solution to this problem?
It turns out that there is – we can use something called a self join. A self join is basically when a join is done on the same table – the best way to think of it is that we have 2 identical copies of the table, and we want to join them based on some predicate. If you need a refresher on join predicates (or just joins in general) then check this link out: Inner vs Outer joins.
Now, the key question is what would be our join predicate in this example? Well, we want to find all the employees who have the same location as Joe – so if we are doing a join we would want to make sure that the location is the same and that the employee name is Joe. So, our join predicate would be where e1.employee_location = e2.employee_location AND employee_name = "Joe". Note that e1 and e2 will represnt the 2 employee tables that we are doing a self join on. Now, here is what the SQL for a self join would look like to solve this problem:

SELECT e1.employee_name FROM employee e1, employee e2 WHERE e1.employee_location = e2.employee_location AND e2.employee_name="Joe";

This query will return the names Joe and Jack – since Jack is the only other person who lives in New York like Joe.
Generally, queries that refer to the same table can be greatly simplified by re-writing the queries as self joins. And, there is definitely a performance benefit for this as well.

Find Nth Highest Record from Database Table

For particular example of employee :

How to get 1st, 2nd, 3rd, 4th, nth topmost salary from an Employee table

The following solution is for getting 6th highest salary from Employee table ,
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
You can change and use it for getting nth highest salary from Employee table as follows
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
where n > 1 (n is always greater than one)

Use of Temp Table and Split Function

 Cursor in Sql

Store temporary data in Stored Procedure in SQL

 How to Retrieve Last Inserted Identity of Record

Split Function

Create FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))    
    returns @temptable TABLE (items varchar(8000))    
    as    
    begin    
        declare @idx int    
        declare @slice varchar(8000)    
       
        select @idx = 1    
            if len(@String)<1 or @String is null  return    
       
        while @idx!= 0    
        begin    
            set @idx = charindex(@Delimiter,@String)    
            if @idx!=0    
                set @slice = left(@String,@idx - 1)    
            else    
                set @slice = @String    
           
            if(len(@slice)>0)
                insert into @temptable(Items) values(@slice)    
   
            set @String = right(@String,len(@String) - @idx)    
            if len(@String) = 0 break    
        end
    return    
    end

Using this Split function in Stored Procedure with temp table

Create proc [dbo].[Dinner_SelectIngredientWeightByOrder]

@OrderIDs varchar(max)--Pass comma seperated Orderid

as

begin

declare @query varchar(max)

CREATE TABLE #tempWeight(

Weight decimal(18,4),

ItemId int,

ItemName varchar(100) )

DECLARE  @RecipeID int 

DECLARE Order_cursor CURSOR FOR 

Select Recipeid from dinner_orderdetails 

where  orderid in (

    select  * from dbo.split(@OrderIDs,',')  

)

OPEN Order_cursor;

FETCH NEXT FROM Order_cursor 

INTO @RecipeID;

WHILE @@FETCH_STATUS = 0

BEGIN

insert into #tempWeight

select Sum(weight) 'Weight',itemid,

(select itemname from inv where itemid=ri.itemid )as IngredientName

from recpinv ri

where recipeid =@RecipeID group by itemid

print '----------'

FETCH NEXT FROM Order_cursor 

    INTO @RecipeID;

END

CLOSE Order_cursor;

DEALLOCATE Order_cursor;

select Sum(Weight)as Weight,itemname from #tempWeight group by itemname

drop table #tempWeight

end