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












0















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.










share|improve this question

























  • Can you please provide some sample data and an expected output.

    – Tony
    Nov 25 '18 at 18:04
















0















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.










share|improve this question

























  • Can you please provide some sample data and an expected output.

    – Tony
    Nov 25 '18 at 18:04














0












0








0








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.










share|improve this question
















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.







sql oracle oracle-apex






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 25 '18 at 21:14









Mihai Chelaru

2,282101222




2,282101222










asked Nov 25 '18 at 17:57









Mooney_SMooney_S

51




51













  • Can you please provide some sample data and an expected output.

    – Tony
    Nov 25 '18 at 18:04



















  • Can you please provide some sample data and an expected output.

    – Tony
    Nov 25 '18 at 18:04

















Can you please provide some sample data and an expected output.

– Tony
Nov 25 '18 at 18:04





Can you please provide some sample data and an expected output.

– Tony
Nov 25 '18 at 18:04












1 Answer
1






active

oldest

votes


















3














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');





share|improve this answer
























  • This worked, thankyou :)

    – Mooney_S
    Nov 25 '18 at 18:17











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53470287%2fdisplay-row-from-a-table-if-a-condition-in-another-table-is-met%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









3














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');





share|improve this answer
























  • This worked, thankyou :)

    – Mooney_S
    Nov 25 '18 at 18:17
















3














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');





share|improve this answer
























  • This worked, thankyou :)

    – Mooney_S
    Nov 25 '18 at 18:17














3












3








3







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');





share|improve this answer













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');






share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 25 '18 at 18:05









user7294900user7294900

22.8k113361




22.8k113361













  • This worked, thankyou :)

    – Mooney_S
    Nov 25 '18 at 18:17



















  • This worked, thankyou :)

    – Mooney_S
    Nov 25 '18 at 18:17

















This worked, thankyou :)

– Mooney_S
Nov 25 '18 at 18:17





This worked, thankyou :)

– Mooney_S
Nov 25 '18 at 18:17




















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53470287%2fdisplay-row-from-a-table-if-a-condition-in-another-table-is-met%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

404 Error Contact Form 7 ajax form submitting

How to know if a Active Directory user can login interactively

TypeError: fit_transform() missing 1 required positional argument: 'X'