CODEWITHSUNDEEP
sqlsql-server-2012
user1591668
user1591668

Reputation: 2893

SQL How Can I get rows that do not match a given condition

I have 2 Tables Facilities and Appointments . Both Tables share a common column FacilityID . How can I get the rows from the Facility table that are not in the Appointment condition ... This is my sql. I want to get the Facilities from the Facility table that are not in the Appointment table during that time period. I am using MSSQL 2012

SELECT A.FacilityID,A.ApptDateTime 
From Appointment A 
WHERE 
    A.FacilityID Not In(SELECT F.FacilityID FROM Facility F WHERE F.FacilityID = A.FacilityID) 
    and
    A.ApptDateTime  between '2-19-2018' and '2-23-2018'  
order by A.FacilityID

Upvotes: 0

Views: 91

Answers (2)

shawnt00
shawnt00

Reputation: 17953

Reverse the inner and outer queries:

SELECT FacilityID FROM Facility
WHERE FacilityID NOT IN (
    SELECT FacilityID
    FROM Appointment
    WHERE ApptDateTime BETWEEN '2-19-2018' AND '2-23-2018
    -- AND FacilityID IS NOT NULL /* be careful if this is nullable */
)
ORDER BY FacilityID;

It's also possible to use an outer join if you specify the narrow the scope of the join by filtering inside the join condition:

SELECT F.FacilityID
FROM Facility F LEFT OUTER JOIN Appointment A
    ON     A.FacilityID = F.FacilityID
       AND ApptDateTime BETWEEN '2-19-2018' AND '2-23-2018
ORDER BY F.FacilityID;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Either use not exists or a correlated subquery, not both:

SELECT A.FacilityID, A.ApptDateTime 
FROM Appointment A 
WHERE NOT EXISTS (SELECT 1
                  FROM Facility F
                  WHERE F.FacilityID = A.FacilityID
                 ) AND
      A.ApptDateTime BETWEEN '2018-02-19' and '2018-02-23'  
ORDER BY A.FacilityID;

Upvotes: 1

Related Questions