Retrieving a list of patients that have visited certain hospitals I have a table of patients that has a field for which hospitals they visited.  I want to retrieve a list of patients that have visited Green Hospital and have also visited other hospitals. Is this query accurate?

SELECT COUNT (DISTINCT PatientID) FROM Patients
WHERE PatientID IN (
        SELECT PatientID FROM Patients WHERE Facility = 'Green'
) AND Facility &lt;&gt; 'Green'


My logic is that the internal statement will get all patients that have visited Green Hospital.  The external will find the intersect of those that visited somewhere else.  I can't think of any way to validate my results which is why I'm posting here.

The DBMS is actually Intersystems Caché.
Using IN tends to have a performance hit in most DBMS, as it typically results in a row by row lookup.  As a rule of thumb, I don't use a subquery if I can express the relationship in a simple join.  I also try to stay away from DISTINCT wherever possible.  When I need to dedup records, I'll typically use a GROUP BY and return the count of records, since it's often useful in my field (reporting) and/or for QA.  For example, the below query should return the patients you are looking for, with the added benefit of how many other hospitals were visited.  You could also do a plus one for total hospitals visited (including "Green").  That may or may not be useful for your needs.

SELECT
    green.PatientID,
    COUNT(1) AS OtherHospitals,
    COUNT(1)+1 AS TotalHospitals
FROM
    Patients green
JOIN
    Patients other ON other.PatientID = green.PatientID
WHERE
    green.Facility = 'Green'
    AND other.Facility &lt;&gt; 'Green'
GROUP BY 
    green.PatientID