Posted by Erland Sommarskog on 12/09/05 21:43

(J.Balakumar@gmail.com) writes:
> I am balakumar here,
> I have created a database of a company like the whole company employee
> is under MD.
> but i want number of employee forming a branches who is directly
> reporting to there immediate senior.
> I want the maximum number of subordinates reporting to their
> immediate(Direct) senior.
> what would be the <syntax> in nested select option.
> I request you to send the <syntax> to my mail ID at the earliest.

A standard recommendation for this type of questions, is that your post
should include:

o CREATE TABLE statement for your table(s).
o INSERT statements with sample data.
o The desired result given the sample.

With that information, your chances to get a tested solution are very
good.

Without that information, and only a vague question, all you get are
more or less guesses. It sounds as if you are looking for how to run
a hierarchical query. In SQL 2000, there is on built-in syntax for this,
but look at
http://groups.google.com/group/comp.databases.ms-sqlserver/browse_thread/thread/dd4678c820576424/bd496712c9795f8f?lnk=st&q=folder+group%3Acomp.databases.ms-sqlserver+author%3ASommarskog&rnum=2&hl=sv#bd496712c9795f8f
for a way of doing it.

In SQL 2005 you can use something called Common Table Expression. If
you have SQL 2005, I suggest that you look up "WITH common table expression"
in Books Online for a discussion and some examples.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]