Select Value From Table 1 That is NULL From Table 2

Jes Sie

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.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ashutosh Gupta6y ago
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')
-1
Amit Mohanty6y ago
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')
0
Jes Sie6y ago
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:

0
Ronika Jency6y ago
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.
0