Hello, I am trying to get list of students who don't have sibling in one building. Below is my query. When I take out the Not in the Not In statement I get data to return. It gives me the list of students that have siblings in the other building. I would like the opposite. I am just learning to SQL queries at the beginners stage. Thank you for your help. Jeremy Code (Text): SELECT s.LASTFIRST, s.FAMILY_IDENT, s.SCHOOLID FROM Students s WHERE ( s.ENROLL_STATUS = 0 AND s.schoolID IN (2,3,4) AND s.FAMILY_IDENT Not IN ( SELECT DISTINCT s.Family_Ident From Students s WHERE S.SchoolId = 2 ) ) ORDER BY s.LASTFIRST

You might be trying to do correlated query . By the way your query is partially right. Refer the following link for More details . Code (Text): SELECT s1.LASTFIRST, s1.FAMILY_IDENT, s1.SCHOOLID FROM Students s1 WHERE s1.ENROLL_STATUS = 0 AND s1.schoolID IN (2,3,4) AND 1 = ( SELECT s2.Family_Ident FROM Students s2 WHERE s2.SchoolId = s1.SchoolId AND s2.FAMILY_IDENT = s1.FAMILY_IDENT ) ORDER BY s1.LASTFIRST Or using IN operator as follows Code (Text): SELECT s1.LASTFIRST, s1.FAMILY_IDENT, s1.SCHOOLID FROM Students s1 WHERE s1.ENROLL_STATUS = 0 AND s1.schoolID IN (2,3,4) AND (s1.schoolID,s1.Family_Ident) NOT IN ( SELECT s2.schoolID,s2.Family_Ident From Students s2 Group by s2.schoolID,s2.Family_Ident Having Count(*) >1 ) ORDER BY s1.LASTFIRST Get back to us in case , your are trying for something else with create and insert table script for for generating example as suggested by Arju.

You can also try this solution Code (Text): SELECT s.lastfirst, s.family_ident, s.schoolid FROM (SELECT * FROM students WHERE schoolid IN (2, 3, 4)) s LEFT JOIN (SELECT * FROM students WHERE schoolid IN (2)) s2 ON s.family_ident = s2.family_ident WHERE s2.family_ident IS NULL ORDER BY s.lastfirst