CODEWITHSUNDEEP
sqlsql-serversqlcommand
Hamid Reza
Hamid Reza

Reputation: 2973

How to set an if on a select for returning select in sql?

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 have written this select on User table:

select u.* from [User] u inner join ParentUser p on u.UserId=p.UserId where p.ParentId=2440

Now I wanna add another column to u.* containing 0 or 1.0 is for users who have some children in ParentUser and 1 is for those whom dont have any.

How to handle this?

Update

enter image description here

Upvotes: 0

Views: 85

Answers (3)

Disillusioned
Disillusioned

Reputation: 14832

It's fairly easy to determine each user that has children. The following starts out by determining the number of children, but in final output (with other columns from user) simply reduces this to: "Are there any children?"

/************************* Set up sample data *************************/
declare @User table (
    UserId int,
    UserName varchar(10)
)
insert @User
select 1, 'A' union all
select 2, 'B' union all
select 3, 'C'

declare @ParentUser table (
    UserId int,
    ParentId int
)
insert @ParentUser
select 3, 1 union all
select 3, 2 union all
select 2, 1


/****************************** Solution ******************************/
;with ChildCounts as (
    select  p.ParentId, COUNT(*) as ChildCount
    from    @ParentUser p
    group by p.ParentId
), UsersWithHasChildren as (
    select  u.*,
            case
                when p.ChildCount > 0 then 1
                else 0
            end as HasChildren
    from    @User u
            left join ChildCounts p on
                p.ParentId = u.UserId
)
select  *
from    UsersWithHasChildren u

From your duplicate question, the special case of determining which children of a particular parent also have children is a trivial WHERE filter as below.

where   u.UserId in (
            select  p.UserId
            from    @ParentUser p
            where   p.ParentId = 1 /*Parametrise this value*/
        )

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93724

You need LEFT JOIN

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
                  WHERE  ParentId IN (SELECT UserId
                                      FROM   ParentUser
                                      WHERE  parentId = 2440)) p
              ON u.UserId = p.ParentId

Upvotes: 3

Juozas
Juozas

Reputation: 935

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

And, if you want to filter by parent id:

 DECLARE @ParentId INT = 2;

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;

Upvotes: 1

Related Questions