CODEWITHSUNDEEP
sqloracle-database
Monomoni
Monomoni

Reputation: 435

oracle SQL: selecting distinct value where it does not contain another certain value in all its record

This is the current database record that I have, and I only want it to display all the name of students who have never been enrolled in Science.

Name        | Subject     | Year
-----------------------------------
Ian Lee     | Math        | 2008
Ian Lee     | Science     | 2008
Ian Lee     | Econs       | 2006
Marie-Ann   | Geography   | 2006
Marie-Ann   | Literature  | 2009
Natalie S.  | Geography   | 2006
Julienne    | Math        | 2008
Julienne    | Science     | 2008
Julienne    | Literature  | 2009
Liam        | Literature  | 2009
Liam        | Econs       | 2006

I also have a student record Emily Toh that has not been enrolled in any classes yet. But the correct output should be

Name
------------
Marie-Ann
Natalie S.
Emily Toh
Liam

This was what I used to call

SELECT DISTINCT en.Name
FROM ENROLLMENT en
WHERE NOT EXISTS (
    SELECT st.Name
    FROM STUDENT st
    WHERE en.Name = st.Name
    AND en.Subject = 'Science'
);

But it still gives me a display of all the student names.

The student table and enrollemnt table is as per:

CREATE TABLE STUDENT(
    Name       VARCHAR2(50),
    DOB        DATE,
    Address    VARCHAR(70),
CONSTRAINT STUDENT_PKEY PRIMARY KEY (Name)
);

CREATE TABLE ENROLLMENT(
    Name       VARCHAR2(50),
    Subject    VARCHAR2(70),
    Year       Number(4),
CONSTRAINT ENROLLMENT_PK PRIMARY KEY (Name, Subject)
CONSTRAINT ENROLLMENT_FKEY FOREIGN KEY (Name) REFERENCES TO STUDENT (Name)
);

Upvotes: 1

Views: 1216

Answers (3)

MT0
MT0

Reputation: 168671

If you do not need to worry about students who have not enrolled in any classes then you can use GROUP BY ... HAVING ... like this:

SELECT   name
FROM     enrollment
GROUP BY name
HAVING   COUNT( CASE WHEN subject = 'Science' THEN 1 ELSE NULL END ) = 0;

If you have students who have not enrolled for any classes and you want to include them in your output then:

SELECT   s.name
FROM     student s
         LEFT OUTER JOIN
         enrollment e
         ON ( s.name = e.name AND e.subject = 'Science' )
GROUP BY s.name
HAVING   COUNT( e.subject ) = 0;

Since name and subject are the composite primary key for the table then the GROUP BY and HAVING clauses could be replaced with a subject IS NULL check:

SELECT   s.name
FROM     student s
         LEFT OUTER JOIN
         enrollment e
         ON ( s.name = e.name AND e.subject = 'Science' )
WHERE    e.subject IS NULL;

Upvotes: 0

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

The approach you made is right, just start with STUDENTS table, and look for the science enrollment using NOT EXISTS.

SELECT  st.Name
FROM STUDENT st
WHERE NOT EXISTS (
    SELECT st.Name
    FROM enrollment en
    WHERE en.Name = st.Name
    AND en.Subject = 'Science'
);

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

One option is to use a subquery to identify all students which you don't want in your result set (i.e. those who were enrolled in Science at some point), and then join to filter off these students.

SELECT s.Name      -- , s.Id
FROM STUDENT s
LEFT JOIN
(
    SELECT Name    -- , Id
    FROM ENROLLMENT
    WHERE Subject = 'Science'
) t
    ON s.Name = t.Name   -- AND s.Id = t.Id
WHERE t.Name IS NULL

This approach will capture both students who are enrolled in no classes and students who enrolled, but never enrolled in Science.

Ideally there should also be an Id column associated with each student in both tables. Without this, joining in a meaningful way could be difficult if two or more students were to share the same name.

Upvotes: 0

Related Questions