CODEWITHSUNDEEP
sqloracle-databaseoracle-apex
Mooney_S
Mooney_S

Reputation: 27

Display row from a table if a condition in another table is met

I have two tables, students and applications. A student has an ID and a name, and can make many applications. The applications table has an ID, the student ID as a FK, and an APPLICATION_STATUS.

I want to display all students where none of their applications have 'APPLICANT ACCEPTED OFFER' in APPLICATION_STATUS.

I've tried:

SELECT STUDENTS.FIRST_NAME ||' '||STUDENTS.LAST_NAME "Student Name"
FROM STUDENTS
JOIN APPLICATIONS ON STUDENTS.SRN = APPLICATIONS.SRN
WHERE APPLICATION_STATUS != 'APPLICANT ACCEPTED OFFER';

This is what I've tried so far, but instead of displaying the students that haven't accepted an offer, it is showing all applications that were not accepted.

Upvotes: 0

Views: 39

Answers (1)

Ori Marko
Ori Marko

Reputation: 58902

You don't need a join, use not exists

 SELECT STUDENTS.FIRST_NAME ||' '||STUDENTS.LAST_NAME "Student Name"
 FROM STUDENTS
 WHERE not exists ( select 1 from 
    APPLICATIONS where STUDENTS.SRN = APPLICATIONS.SRN
    And APPLICATION_STATUS = 'APPLICANT ACCEPTED OFFER');

Upvotes: 3

Related Questions