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

kevinf

SOLVED CLOSED ACTIVE ANSWERED
User: kevinf
Posted: on Sep 01, 2022 12:41 AM
Forum: SQL Server
Answer: 1
Views: 1317

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

PrinceG

0 3

Reply
ANSWER
User: PrinceG
Replied: on Sep 01, 2022 07:44 AM
Report

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

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

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

Company

Explore

Follow us

Contact Us