Results 1 to 5 of 5

Thread: Query Help

  1. #1
    Join Date
    Oct 2008
    Posts
    1

    Unanswered: Query Help

    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

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    You have the same table "alias" on both the query and sub-query, try changing this:
    Code:
    -- etc --
    AND s.FAMILY_IDENT Not IN ( SELECT DISTINCT F.Family_Ident
    From Students F
    WHERE F.SchoolId = 2
    ) --etc --
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Oct 2004
    Posts
    8
    Hi, lovelandj. Maybe none of the students has any sibling in the other building. Try to check the data in the Students table.

  4. #4
    Join Date
    Oct 2008
    Posts
    2

    Check the data

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

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Another potential solution
    Code:
    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
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •