CODEWITHSUNDEEP
sql-server
Libin Jose
Libin Jose

Reputation: 1

sql parent child relationship query

I am having a customer table with following columns

 CustomerID, CustomerName, ParentCustomerID

How to get the customer who has the most number of children from this table?

Upvotes: 0

Views: 618

Answers (4)

perfectionist
perfectionist

Reputation: 4346

I think you'll want this.

select top 1
    PARENT.CustomerID,
    NumberOfChildren = count(CHILD.CustomerID)
from customers PARENT
left join customers CHILD on PARENT.CustomerID = CHILD.ParentCustomerID
group by PARENT.CustomerID 
order by NumberOfChildren desc

This avoids the need for sub-queries by left joining the table to itself and can be quite efficient.

Upvotes: 1

Andreas Rohde
Andreas Rohde

Reputation: 609

Try this Stamtent to solve the problem

SELECT TOP 1 ParentCustomerID, COUNT(customerID)
FROM customer 
GROUP BY ParentCustomerID

Upvotes: 0

Mithrandir
Mithrandir

Reputation: 25397

You could try something like this:

;WITH CTE( ChildCount, ParentCustomerID)
AS
(
  SELECT 
          COUNT(customerID) ChildCount,
          ParentCustomerID 
  FROM TABLE 
  GROUP BY ParentCustomerID
)
SELECT TABLE.CustomerId, TABLE.CustomerName
FROM CTE 
INNER JOIN TABLE 
ON 
TABLE.CustomerId = CTE.ParentCustomerID
AND 
CTE.ChildCount = (SELECT MAX(ChildCount) FROM CTE);

Upvotes: 0

Sam Peacey
Sam Peacey

Reputation: 6034

Something like this should work, though I doubt it's the most efficient way of doing it.

Also I haven't run this myself, so it may need a little massage to get it to work. :)

select customerId, CustomerName
from
    customers
where customerId = (
    select top 1 ParentCustomerID 
    from
        customers
    group by ParentCustomerID
    order by count(*) desc
)

Upvotes: 1

Related Questions