Change my query please

I have two tables, one with basic info about employee and the other one
with hierarchical data (employee ID, supervisor ID)
Following query returns the name, id and child count. Problem is that
it returns the entire table. I am only interested in records whose
supervisor id is 55 (or whatever). So, it should return the names of
employees, count of their sub employees only if their supervisor is 55.
Does this make sense?
SELECT P.First_Name + ' ' + P.Last_Name as 'Name', P.Employee_ID,
COUNT(C.Parent_Employee_ID) AS child_count
FROM Employee AS P LEFT JOIN Employee_Hierarchy AS C
ON P.Employee_ID = C.Parent_Employee_ID
GROUP BY P.Employee_ID, P.First_Name , P.Last_NameYou haven't posted any DDL, but I still think this might be of help:
http://milambda.blogspot.com/2005/0...or-monkeys.html
ML|||hi sehboo,
Just add the criteria for the supervior id:
SELECT P.First_Name + ' ' + P.Last_Name as 'Name', P.Employee_ID,
COUNT(C.Parent_Employee_ID) AS child_count
FROM Employee AS P LEFT JOIN Employee_Hierarchy AS C
ON P.Employee_ID = C.Parent_Employee_ID
where C.SupervisorID=55
GROUP BY P.Employee_ID, P.First_Name , P.Last_Name
HTH
das
"Sehboo" wrote:

> I have two tables, one with basic info about employee and the other one
> with hierarchical data (employee ID, supervisor ID)
>
> Following query returns the name, id and child count. Problem is that
> it returns the entire table. I am only interested in records whose
> supervisor id is 55 (or whatever). So, it should return the names of
> employees, count of their sub employees only if their supervisor is 55.
> Does this make sense?
> SELECT P.First_Name + ' ' + P.Last_Name as 'Name', P.Employee_ID,
> COUNT(C.Parent_Employee_ID) AS child_count
> FROM Employee AS P LEFT JOIN Employee_Hierarchy AS C
> ON P.Employee_ID = C.Parent_Employee_ID
> GROUP BY P.Employee_ID, P.First_Name , P.Last_Name
>|||Get a copy of TREES & HIERARCHIES IN SQL for several better ways to
model a heirarchy.|||If I add
where C.SupervisorID=55
then it shows just one record, I want to see all the children of
supervisor.|||Check this thread:
25e265122fe9c2e7" target="_blank">http://groups.google.com/group/micr...5e265122fe9c2e7
It has an example of how to deal with this using a simple breadth first
loop.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
<MasoodAdnan@.gmail.com> wrote in message
news:1126149763.658813.99930@.z14g2000cwz.googlegroups.com...
> If I add
> where C.SupervisorID=55
> then it shows just one record, I want to see all the children of
> supervisor.
>|||On 7 Sep 2005 20:22:43 -0700, MasoodAdnan@.gmail.com wrote:

>If I add
>where C.SupervisorID=55
>then it shows just one record, I want to see all the children of
>supervisor.
Hi MasoodAdnan,
Try adding
WHERE P.SupervisorID = 55
instead.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)