CODEWITHSUNDEEP
sqlsql-servert-sqlsqlcommand
Hamid Reza
Hamid Reza

Reputation: 2973

How to write a query to check if a child has any children in a table

I have two tables named User and ParentUser that there is an one to many relation between them and the many side is the ParentUser table.

I wanna write a query to pass a parentId and fetch all of its children and a column name HasChildren to see whether every child has any children or not.

The picture below shows some sample data and needed result:

enter image description here

Tries:

1-By Prdp

SELECT u.*,
       CASE
         WHEN p.ParentId IS NULL THEN 1
         ELSE 0
       END as HasChildren
FROM   [User] u
       LEFT JOIN (select distinct ParentId from ParentUser) p
              ON u.UserId = p.ParentId

All good here but I cant pass a parentId to it.

2- By Juozas

DECLARE @ParentId INT = 2441;

SELECT DISTINCT
     [UserId]       =   [u].[userid]
    ,[HasChildren]  =   CAST(ISNULL([pu].[userid], 0) AS BIT)
    ,[ChildrenId]   =   [pu].[userid]
FROM
    [user]  AS  [u]
OUTER APPLY
    (
        SELECT [userid], [parentid] FROM [ParentUser] WHERE [parentid] = [u].[userid]
    )       AS  [pu]
WHERE
        [pu].[parentid] = @ParentId;

All good again but the field HasChildren's value is always 1.

Upvotes: 1

Views: 1084

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460108

SELECT UserId, HasChildren = CASE WHEN EXISTS(SELECT 1 FROM ParentUser pu2
                                              WHERE pu2.ParentId = pu.UserId)
                             THEN 1 ELSE 0 END
FROM ParentUser pu
WHERE ParentId = @ParentId
ORDER BY UserId

Upvotes: 2

Related Questions