[lovelandj]

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


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

[LKBrwn_DBA]

You have the same table "alias" on both the query and sub-query, try changing this:

-- etc --
AND s.FAMILY_IDENT Not IN ( SELECT DISTINCT F.Family_Ident
From Students F
WHERE F.SchoolId = 2
) --etc --

[lwms]

Hi, lovelandj. Maybe none of the students has any sibling in the other building. Try to check the data in the Students table.

[hikrishna007]

your query might be correct. there might not be any sibling in the building which u r looking for

[georgev]

Another potential solution

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