Select Value From Table 1 That is NULL From Table 2

Question

I have 2 tables.

Table1 contains 4 columns (Id, pt_RFID, sessionTime, sessionDate)

Table2 contains 7 columns (Id, pt_RFID, sessionTime, sessionDate, isTrainerShow, isTraineeShow)

Now, what I would like to achieve is to retrieve the sessionTime from Table1 where Table1.pt_RFID =@pt_RFID, Table2.sessionTime IS NULL and Table1.sessionDate = FORMAT (getdate(), 'yyyy-MM-dd').

The problem is, it did not give me the data that I wanted. Below is my actual Table:

Table1

Table2

Below is my query that obviously is not correct:

select
st.sessionTime
from dbo.tblPT_SessionTime st
LEFT JOIN dbo.tblPT_Sessions sd ON sd.sessionTime = st.Id
WHERE sd.sessionTime IS NULL and st.pt_RFID = @pt_RFID AND st.sessionDate = FORMAT (getdate(), 'yyyy-MM-dd')

Below is the result of the above query and should give me 15 rows since the sessionDate of the 1st row from Table2 is not the current date.

I hope someone could assist me.

Amit Mohanty · Answer

Hey try this Select t1.* From tblPT_SessionTime t1 join tblPT_Sessions t2 on t1.pt_RFID=t2.pt_RFID And t1.Id!=t2.sessionTime WHERE t1.pt_RFID = 4 AND t2.sessionDate = FORMAT (getdate(), 'yyyy-MM-dd' )

Jes Sie · Answer

Hi Ronica, Thanks for your response. The reason why I am comparing the data from both table is that I would like only to show the session time from the table1 which is not available in the table2. Removing the sd.sessionTime IS NULL will give you the whole 16 time periods. To make my explanation clear, please refer to the table below:

Ronika Jency · Answer

Hi, why are you comparing with ( ON sd.sessionTime = st.Id) session time to id in left join. And if you need 15 records, you need run below query. I just removed sd.sessionTime IS NULL from your query. select st.sessionTime from dbo.tblPT_SessionTime st LEFT JOIN dbo.tblPT_Sessions sd ON sd.sessionTime = st.id WHERE st.pt_RFID = 4 AND st.sesstionDate = FORMAT (getdate(), 'yyyy-MM-dd') above query will give expect result.

Ashutosh Gupta · Answer

check this out select st.sessionTime from dbo.tblPT_SessionTime st JOIN dbo.tblPT_Sessions sd ON sd.pt_RFID = st.pt_RFID WHERE sd.sessionTime IS NULL AND st.sessionDate = FORMAT (getdate(), 'yyyy-MM-dd' )

Select Value From Table 1 That is NULL From Table 2

Answers (4)