SQL Get Parent when child is column is 0

I have two tables below.

CREATE TABLE Name
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(256)
)

CREATE TABLE BranchAddress
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    UserId INT NOT NULL,
    ParentId INT NOT NULL,
    IsNetwork BIT,
    Address VARCHAR(256)
)

Data:

INSERT INTO dbo.Name ( Name )
VALUES ('George'), ('Greg')

INSERT INTO BranchAddress (UserId, ParentId, IsNetwork, Address)
VALUES (1, 0, 1, 'Kings Street'), (1, 1, 0, 'GS Road'),
       (1, 0, 0, 'Nariman Point'), (1, 1, 1, 'St. College Street'),
       (1, 2, 1, 'PG National Road'), (1, 1, 0, 'LOS India')

Using joins I have got the information from both the tables which have needed, but in a new case I need to extract the Address like if the IsNetwork = 0 then Address should of it’s the parent.

Following is the query of join

SELECT 
    t.Id, t.Name, t2.ParentId, t2.IsNetwork, t2.Address 
FROM 
    dbo.Name t 
INNER JOIN 
    BranchAddress t2 ON t.Id = t2.UserId 

This is the output of that query:

Id          Name    ParentId IsNetwork    Address
----------- ------- ----------- --------- -------------------------
1           George  0           1         Kings Street
1           George  1           0         GS Road
1           George  0           0         Nariman Point
1           George  1           1         St.College Street
1           George  2           1         PG National Road
1           George  1           0         LOS India

I want the output as below.

Id          Name    ParentId IsNetwork    Address
----------- ------- ----------- --------- -------------------------
1           George  0           1         Kings Street
1           George  1           0         Kings Street
1           George  0           0         Kings Street
1           George  1           1         St.College Street
1           George  2           1         PG National Road
1           George  1           0         Kings Street

Answer

You need to JOIN to BranchAddress a second time to get the Address of the parent, then select which address to return based on the IsNetwork value of the original row:

SELECT n.Id, n.Name, b1.ParentId, b1.IsNetwork, 
       CASE WHEN b1.IsNetwork = 0 THEN b2.Address
            ELSE b1.Address
       END AS Address
FROM Name n
INNER JOIN BranchAddress b1 ON n.Id = b1.UserId
LEFT JOIN BranchAddress b2 ON b2.ID = b1.ParentId

Output

Id  Name    ParentId    IsNetwork   Address
1   George  0           true        Kings Street
1   George  1           false       Kings Street
1   George  1           false       Kings Street
1   George  1           true        St. College Street
1   George  2           true        PG National Road
1   George  1           false       Kings Street

Note that the entry for Nariman Point had an invalid ParentId (0) and I’ve changed that to 1 (to be consistent with the expected results) for the demo.

Demo on SQLFiddle