@dharmendr
I solved my issue using LEFT JOIN and Subquery on the email column to get my expected output.
Declare @Users AS TABLE(UserId INT PRIMARY KEY, Email VARCHAR(50), Role VARCHAR(50))
INSERT INTO @Users VALUES(11, 'Test1@gmail.com', 'Parent')
INSERT INTO @Users VALUES(19, 'Test5@gmail.com', 'Student')
INSERT INTO @Users VALUES(20, 'Test2@gmail.com', 'Student')
INSERT INTO @Users VALUES(21, 'Test3@gmail.com', 'Student')
INSERT INTO @Users VALUES(25, 'Test4@gmail.com', 'Parent')
Declare @Transport AS TABLE(TransportID INT IDENTITY PRIMARY KEY, TransportName VARCHAR(50))
INSERT INTO @Transport VALUES('Transport1')
INSERT INTO @Transport VALUES('Transport2')
Declare @Class AS TABLE(ID INT IDENTITY PRIMARY KEY, ClassName VARCHAR(50))
INSERT INTO @Class VALUES('SS 1')
INSERT INTO @Class VALUES('SS 2')
Declare @Class_Section AS TABLE(ID INT IDENTITY PRIMARY KEY, ClassSection VARCHAR(50))
INSERT INTO @Class_Section VALUES('A')
INSERT INTO @Class_Section VALUES('B')
Declare @Hostel AS TABLE(HostelID INT IDENTITY PRIMARY KEY, HostelName VARCHAR(50))
INSERT INTO @Hostel VALUES('Hostel1')
INSERT INTO @Hostel VALUES('Hostel2')
Declare @Students AS TABLE(ID INT IDENTITY PRIMARY KEY, StudentID INT, TransportID INT, HostelID INT
, ClassID INT, Class_SectionID INT,
ParentID INT NULL)
INSERT INTO @Students VALUES(20,1,2,2,2,11)
INSERT INTO @Students VALUES(19,2,2,2,1,NULL)
INSERT INTO @Students VALUES(21,2,1,2,1,25)
Select Case s.StudentID, u.Email,
( SELECT Email
FROM @Users u
WHERE EXISTS(SELECT ParentID FROM @Students s WHERE s.ParentID = u.UserId)
And u.UserId = 25 )as [Student's ParentEmail],
u.UserId, c.ClassName, cs.ClassSection, u.Role, h.HostelName, t.TransportName
FROM @Students s
LEFT JOIN @Users u On s.StudentID = u.UserId
INNER JOIN @Transport t On s.TransportID = t.TransportID
INNER JOIN @Hostel h On s.HostelID = h.HostelID
INNER JOIN @Class_Section cs On s.Class_SectionID = cs.ID
INNER JOIN @Class c On s.ClassID = c.ID
WHERE s.StudentID = 21
Thanks for your input.
