Select all records from table that do not exist in another in SQL Server

kevinf
 
on Sep 01, 2022 12:41 AM
435 Views

Hi 

I have a table with Staff names, 2nd table with leave and 3rd table with rosters.

I need to display from the staff names table any staff not on leave or rostered in sql Select Statement

see code

Select s.ID, s.FirstN, s.LastN, l.Staffid, st.staffid 
FROM StaffMaster as s 
Left Join LeaveTable as l on l.staffid=s.id 
Left Join StaffRoster st on s.id=st.StaffId 
where l.staffid is Null or (l.Fromd Not between '2022-08-01' and '2022-08-03' 
and l.Tod not between '2022-08-01' and '2022-08-03') 
or (st.SDate Not between '2022-08-01' and '2022-08-03') 
order by s.FullN

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
PrinceG
 
on Sep 01, 2022 07:44 AM

Hi Kevinf,

Please refer below sample Query.

SQL

DECLARE @StartDate DATE, @Endate DATE
SET @StartDate = '2022-08-01'
SET @Endate = '2022-08-03'

SELECT s.StaffId, s.FirstName
FROM #StaffMaster AS s
WHERE s.StaffId NOT IN (SELECT DISTINCT StaffId FROM #Roster WHERE RDate BETWEEN @StartDate AND @Endate)
AND s.StaffId NOT IN (SELECT DISTINCT StaffId FROM #LEAVE WHERE SDate >= @StartDate AND EDate <= @Endate)
ORDER BY s.FirstName

Screenshot