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.
sql oracle oracle-apex
edited Nov 25 '18 at 21:14
Mihai Chelaru
2,367101323
asked Nov 25 '18 at 17:57
Mooney_SMooney_S
51
add a comment |
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
edited Nov 25 '18 at 21:14
Mihai Chelaru
2,367101323
asked Nov 25 '18 at 17:57
Mooney_SMooney_S
51
Can you please provide some sample data and an expected output.
– Tony
Nov 25 '18 at 18:04
add a comment |
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
edited Nov 25 '18 at 21:14
Mihai Chelaru
2,367101323
asked Nov 25 '18 at 17:57
Mooney_SMooney_S
51
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
sql oracle oracle-apex
edited Nov 25 '18 at 21:14
Mihai Chelaru
2,367101323
asked Nov 25 '18 at 17:57
Mooney_SMooney_S
51
edited Nov 25 '18 at 21:14
Mihai Chelaru
2,367101323
asked Nov 25 '18 at 17:57
Mooney_SMooney_S
51
edited Nov 25 '18 at 21:14
Mihai Chelaru
2,367101323
edited Nov 25 '18 at 21:14
Mihai Chelaru
2,367101323
edited Nov 25 '18 at 21:14
Mihai Chelaru
2,367101323
2,367101323
asked Nov 25 '18 at 17:57
Mooney_SMooney_S
51
asked Nov 25 '18 at 17:57
Mooney_SMooney_S
51
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
add a comment |
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
add a comment |
1 Answer
1
active
oldest
votes
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');
answered Nov 25 '18 at 18:05
user7294900user7294900
23.4k113464
This worked, thankyou :)
– Mooney_S
Nov 25 '18 at 18:17
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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');
answered Nov 25 '18 at 18:05
user7294900user7294900
23.4k113464
This worked, thankyou :)
– Mooney_S
Nov 25 '18 at 18:17
add a comment |
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');
answered Nov 25 '18 at 18:05
user7294900user7294900
23.4k113464
This worked, thankyou :)
– Mooney_S
Nov 25 '18 at 18:17
add a comment |
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');
answered Nov 25 '18 at 18:05
user7294900user7294900
23.4k113464
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');
answered Nov 25 '18 at 18:05
user7294900user7294900
23.4k113464
answered Nov 25 '18 at 18:05
user7294900user7294900
23.4k113464
answered Nov 25 '18 at 18:05
user7294900user7294900
23.4k113464
answered Nov 25 '18 at 18:05
user7294900user7294900
23.4k113464
23.4k113464
This worked, thankyou :)
– Mooney_S
Nov 25 '18 at 18:17
add a comment |
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
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
Can you please provide some sample data and an expected output.
– Tony
Nov 25 '18 at 18:04