1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL Query Help

Discussion in 'SQL PL/SQL' started by lovelandj, Oct 20, 2008.

  1. lovelandj

    lovelandj Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    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
     
     
  2. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Can you provide table structure, sample data and more precise with example about it?
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    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.
     
  4. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    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