Having issues with SQL Server self join

Question

i have the following :

 USE xxx
 GO

 SET ANSI_NULLS ON
  GO

  SET QUOTED_IDENTIFIER ON
  GO

 SET ANSI_PADDING ON
 GO

 CREATE TABLE [dbo].[branches](
     [branchNumber] [int] NULL,
     [isSub] [char](1) NULL,
     [masterBranch] [int] NULL
 ) ON [PRIMARY]

 GO

SET ANSI_PADDING OFF
GO

insert into branches 
values ( 115, 'Y', 900 ) 


insert into branches 
values ( 123, 'Y', 900 ) 

insert into branches 
values ( 150, 'Y', 900 ) 

insert into branches 
values ( 900, 'N', null ) 

insert into branches 
values ( 901, 'N', null ) 
 go 

I need to check a) Is 115 a isSub = Y? If it is, then I am done return 115. b) Is 900 a IsSub = Y? If not, I need to return : 115, 123, 150, 900. c) is 901 a isSub = Y? If not, I need to return 901.

It has been quite sometime since I looked at rank() function so I am kind of stuck at the moment. Any help will be appreciated. thanks

Answer 1

a simple union seems to work.

select distinct branchNumber as num from dbo.branches
where branchNumber = 900
union
select branchNumber as num from dbo.branches
where masterBranch = 900