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 tuanbusku, Jun 4, 2018.

  1. tuanbusku

    tuanbusku Newly Initiated

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    136phanboichau
    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. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,607
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Post your data and tables -- no one can help without it.
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    773
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    Provide here your data and tables.
    For an example and education and testing :
    Code (SQL):

    -- with group by
    SELECT
      MAX(s.lastfirst) keep(dense_rank FIRST ORDER BY s.schoolid) lastfirst,
      s.family_ident,
      s.schoolid
    FROM
      students s
    WHERE
      s.enroll_status = 0
      AND s.schoolid IN (2,3,4)
    GROUP BY
      s.family_ident, s.schoolid
    HAVING
      COUNT(1) = 1
    ORDER BY
      s.lastfirst;


    -- with not exists
    SELECT
      s.lastfirst,
      s.family_ident,
      s.schoolid
    FROM students s
    WHERE
    s.enroll_status = 0
    AND s.schoolid IN (2,3,4)
    AND NOT EXISTS ( SELECT 1 FROM students t WHERE t.schoolid != s.schoolid AND t.family_ident = s.family_ident)
    )
    ORDER BY
    s.lastfirst;

    -- with fact sub_query
    WITH
    family_idents AS
    (
    SELECT t.schoolid,t.family_ident
    FROM students t
      WHERE t.schoolid IN (2,3,4)
        AND t.enroll_status = 0
    GROUP BY t.schoolid,t.family_ident
    HAVING COUNT(1) = 1
    )
    SELECT
      s.lastfirst,
      s.family_ident,
      s.schoolid
    FROM students s,family_idents fi
    WHERE
      s.schooldid = fi.schooldid
      AND s.family_ident = fi.family_ident
    ORDER BY s.lastfirst;

    -- with analitycs function
    WITH
    people_list AS
    (
    SELECT
      s.lastfirst,
      s.family_ident,
      s.schoolid,
      ROW_NUMBER() OVER (partition BY   s.family_ident ORDER BY s.school_id) cnt_fam_ident
    FROM students s
    WHERE
    s.enroll_status = 0
    AND s.schoolid IN (2,3,4)
    )
    SELECT
     p.*
    FROM
      people_list p
    WHERE p.cnt_fam_ident = 1;
    ORDER BY s.lastfirst;