Click here to Skip to main content
14,880,122 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I am having a customer table as

CustomerID AccountNumber ParentCustomerID
- -------- ------------- -----------------

from this how can i find the customer which has most number of children ?

1 solution

You can use Left Outer Join Statment

Select top 1 C.CustomerId,IsNull(Count(P.*),0)
Left Outer Join YOUR_PARENT_TABLE P On ( A.ParentCustomerID= P.ParentCustomerID)
Order By IsNull(Count(P.*),0)desc

omit top 1 if you want to see all record
Libin Jose Chemperi 19-Jan-12 5:34am
can we applay IsNull to a non field member?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900