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

Last Reply 3 months ago By dharmendr

Posted 3 months ago

I have a tblStudent like this

AdmissionNo

SName

FName

Phone

R-01

ABC

XYZ

123456789

R-02

SKY

ABC

123456789

R-03

XYZ

SKY

123456789

and 

tblAttendance

AdmissionNo

ADate

CheckIn

CheckOut

Status

Now I wrote the following Query:

SELECT c.AdmissionNo, c.SName,c.FName, RollNo,ADate,o.Remarks,CheckIn,CheckOut 
FROM tblStdReg as c 
LEFT JOIN tblAttendance as o ON c.AdmissionNo = o.AdmissionNo 
WHERE c.AdmissionNo is not NULL

Now it is showing me output like this

AdmissionNo

SName

ADate

CheckIn

CheckOut

Status

R-01

ABC

NULL

NULL

NULL

NULL

R-02

SKY

NULL

NULL

NULL

NULL

R-03

XYZ

NULL

NULL

NULL

NULL

After inserting the data it looks like this.

tblAttendance

AdmissionNo

ADate

CheckIn

CheckOut

Status

R-01

12.08.2019

8:00 AM

1:00 PM

Present

R-02

12.08.2019

8:00 AM

1:00 PM

Present

R-03

12.08.2019

8:00 AM

1:00 PM

Present

Now Once again run this same query for the next date 13.08.2019.it is showing me like this.

AdmissionNo

SName

ADate

CheckIn

CheckOut

Status

Whereas my requirements is that for taking attendance in each new date student data must be display like this way.

AdmissionNo

SName

ADate

CheckIn

CheckOut

Status

R-01

ABC

NULL

NULL

NULL

NULL

R-02

SKY

NULL

NULL

NULL

NULL

R-03

XYZ

NULL

NULL

NULL

NULL

Posted 3 months ago Modified on 3 months ago

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

AdmissionNoSNameADateCheckInCheckOutRemarks
R-02 SKY NULL NULL NULL NULL
R-03 XYZ NULL NULL NULL NULL