Query to select records with NULL values using Join in SQL Server

Hi smile,

You have to use sub query. With join it is not possible.

Check the below query.

SQL

DECLARE @Date VARCHAR(10)
SET @Date = '13.08.2019'
DECLARE @tblStudent AS TABLE(AdmissionNo VARCHAR(20),SName VARCHAR(10),FName VARCHAR(10),Phone INT)
INSERT INTO @tblStudent VALUES('R-01','ABC','XYZ',123456789)
INSERT INTO @tblStudent VALUES('R-02','SKY','ABC',123456789)
INSERT INTO @tblStudent VALUES('R-03','XYZ','SKY',123456789)
 
DECLARE @tblAttendance AS TABLE(AdmissionNo VARCHAR(20),ADate VARCHAR(10),CheckIn VARCHAR(10),CheckOut VARCHAR(10),Remarks VARCHAR(10))
INSERT INTO @tblAttendance VALUES('R-01','12.08.2019','8:00 AM','1:00 PM','Present')
INSERT INTO @tblAttendance VALUES('R-02','12.08.2019','8:00 AM','1:00 PM','Present')
INSERT INTO @tblAttendance VALUES('R-03','12.08.2019','8:00 AM','1:00 PM','Present')
INSERT INTO @tblAttendance VALUES('R-01','13.08.2019','8:00 AM','1:00 PM','Present')
 
SELECT * FROM (
SELECT c.AdmissionNo,c.SName,
        (SELECT o.ADate FROM @tblAttendance as o WHERE o.AdmissionNo = c.AdmissionNo AND o.ADate = @Date) ADate,
        (SELECT o.CheckIn FROM @tblAttendance as o WHERE o.AdmissionNo = c.AdmissionNo AND o.ADate = @Date) CheckIn,
        (SELECT o.CheckOut FROM @tblAttendance as o WHERE o.AdmissionNo = c.AdmissionNo AND o.ADate = @Date) CheckOut,
        (SELECT o.Remarks FROM @tblAttendance as o WHERE o.AdmissionNo = c.AdmissionNo AND o.ADate = @Date) Remarks
FROM @tblStudent as c
WHERE c.AdmissionNo IS NOT NULL) t
WHERE t.Remarks IS NULL

Output