Udjuni.com

Mathematics

Science

Computing

Accounting

Business

History

Agriculture

Biology

Joining a table with itself (SQL self-join)

Joining a table with itself is sometimes the only way to make sense of information. Consider a scenario where everybody's information in a company including managers and ordinary employees. That same table contains information on who reports to who? but this information. Such information is only indicated by using the manager's employee id as indicated in the table bellow:

If you were using the default Microsoft SQL server Northwind database, you could issue the following query to get the table bellow:

select employeeid, firstname, lastname, reportsto

from employees



Employeeid	FirstName	LastName	Reportsto

==========================================================

1		Nancy		Davolio		2

2		Andrew		Fuller		NULL

3		Janet		Leverling	2

4		Margaret	Peacock		2

5		Steven		Buchanan	2

6		Michael		Suyama		5

7		Robert		King		5

8		Laura		Callahan	2

9		Anne		Dodsworth	5

Now, to see the name of supervisors and the employees they supervise, you issue the following query:



 select a.employeeid, a.firstname, a.lastname, a.reportsto,

        b.firstname as fname, b.lastname as lname

 from employees a left outer join  employees b on  a.reportsto = b.employeeid



employeeid	firstName	LastName   Reportsto    fname   Lname

=========================================================================

1		Nancy		Davolio		2	Andrew	Fuller

2		Andrew		Fuller		NULL	NULL	NULL

3		Janet		Leverling	2	Andrew	Fuller

4		Margaret	Peacock		2	Andrew	Fuller

5		Steven		Buchanan	2	Andrew	Fuller

6		Michael		Suyama		5	Steven	Buchanan

7		Robert		King		5	Steven	Buchanan

8		Laura		Callahan	2	Andrew	Fuller

9		Anne		Dodsworth	5	Steven	Buchanan

Now you can see that most people reports to Andrew Fuller, including Steven Buchanan who has his own subordinates. Notice that Andrew himself does not report to anybody (because there are NULL values in reportsto, fname and Lname).

The state of Periodic table elements
- In addition to the group 8 or O elements, the following elements also naturally occur in gas state: H, N, O, F, Cl and Br -Among metals and non-metals, the following occurs in liquid state: Na, K, Rb, Cs, Fr, Hg a

breaking code periodic table

Copying data from one table to the other
here are several ways to copy or move data from one table to another. Some methods are applicable when copying data from one table to another on the same database and other methods are applicable for copying data from one database to another and even or a different server. Bellow are several methods

SQL cross and equi-joins
In the posting , Selecting data from Multiple tables, we briefly covered ways of selecting data from

SQL inner and outer joins
Just like we saw in SQL cross and equi-Joins, SQL is pretty powerful at gathering and manipulating data from several tables. Now we are going to talk

Udjuni.com

Joining a table with itself (SQL self-join)

RELATED ARTICLES: