How do I do a join and exclude certain records?
Table STUDENT has columns ID, LASTNAME, FIRSTNAME
Table TEACHER has columns ID, TEACHER_ID, STUDENT_ID
Students can have many teachers. Teachers can add students to their class.
I want to be able to list all students available to add for a teacher. Once the teacher adds that student, they are no longer available to see on their list; however, other teachers can still see the student so they can add them to their list.
What would the query look like for the view page? I would like for the view page to be the same page for all teachers and the data to only change.
SELECT ID, LASTNAME, FIRSTNAME
FROM STUDENTS
LEFT JOIN TEACHERS ON TEACHERS.ID = STUDENTS.ID
WHERE TEACHERS.ID <> $teacherID ???
This is where I get confused. It doesn't return results except for the ones in TEACHERS table that don't have the $teacherID (that's not how I want it to work). How Do I pick all students except the one's that the current teacher has already added?
I'm coding in PHP and using DQL, but any help on just the query part would be great.
Hope that makes sense. I confuse myself.
mysql join mariadb
asked Feb 9 '16 at 17:50
stormchaserstormchaser
34
add a comment |
Table STUDENT has columns ID, LASTNAME, FIRSTNAME
Table TEACHER has columns ID, TEACHER_ID, STUDENT_ID
Students can have many teachers. Teachers can add students to their class.
I want to be able to list all students available to add for a teacher. Once the teacher adds that student, they are no longer available to see on their list; however, other teachers can still see the student so they can add them to their list.
What would the query look like for the view page? I would like for the view page to be the same page for all teachers and the data to only change.
SELECT ID, LASTNAME, FIRSTNAME
FROM STUDENTS
LEFT JOIN TEACHERS ON TEACHERS.ID = STUDENTS.ID
WHERE TEACHERS.ID <> $teacherID ???
This is where I get confused. It doesn't return results except for the ones in TEACHERS table that don't have the $teacherID (that's not how I want it to work). How Do I pick all students except the one's that the current teacher has already added?
I'm coding in PHP and using DQL, but any help on just the query part would be great.
Hope that makes sense. I confuse myself.
mysql join mariadb
asked Feb 9 '16 at 17:50
stormchaserstormchaser
34
add a comment |
Table STUDENT has columns ID, LASTNAME, FIRSTNAME
Table TEACHER has columns ID, TEACHER_ID, STUDENT_ID
Students can have many teachers. Teachers can add students to their class.
I want to be able to list all students available to add for a teacher. Once the teacher adds that student, they are no longer available to see on their list; however, other teachers can still see the student so they can add them to their list.
What would the query look like for the view page? I would like for the view page to be the same page for all teachers and the data to only change.
SELECT ID, LASTNAME, FIRSTNAME
FROM STUDENTS
LEFT JOIN TEACHERS ON TEACHERS.ID = STUDENTS.ID
WHERE TEACHERS.ID <> $teacherID ???
This is where I get confused. It doesn't return results except for the ones in TEACHERS table that don't have the $teacherID (that's not how I want it to work). How Do I pick all students except the one's that the current teacher has already added?
I'm coding in PHP and using DQL, but any help on just the query part would be great.
Hope that makes sense. I confuse myself.
mysql join mariadb
asked Feb 9 '16 at 17:50
stormchaserstormchaser
34
Table STUDENT has columns ID, LASTNAME, FIRSTNAME
Table TEACHER has columns ID, TEACHER_ID, STUDENT_ID
Students can have many teachers. Teachers can add students to their class.
I want to be able to list all students available to add for a teacher. Once the teacher adds that student, they are no longer available to see on their list; however, other teachers can still see the student so they can add them to their list.
What would the query look like for the view page? I would like for the view page to be the same page for all teachers and the data to only change.
SELECT ID, LASTNAME, FIRSTNAME
FROM STUDENTS
LEFT JOIN TEACHERS ON TEACHERS.ID = STUDENTS.ID
WHERE TEACHERS.ID <> $teacherID ???
This is where I get confused. It doesn't return results except for the ones in TEACHERS table that don't have the $teacherID (that's not how I want it to work). How Do I pick all students except the one's that the current teacher has already added?
I'm coding in PHP and using DQL, but any help on just the query part would be great.
Hope that makes sense. I confuse myself.
mysql join mariadb
mysql join mariadb
asked Feb 9 '16 at 17:50
stormchaserstormchaser
34
asked Feb 9 '16 at 17:50
stormchaserstormchaser
34
asked Feb 9 '16 at 17:50
stormchaserstormchaser
34
asked Feb 9 '16 at 17:50
stormchaserstormchaser
34
asked Feb 9 '16 at 17:50
stormchaserstormchaser
34
34
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
A unique constraint on TEACHER_ID, STUDENT_ID would prevent duplicate STUDENT_ID for a teacher. In fact you could just make the the PK and drop ID.
This is TSQL so it may be wrong for mysql
SELECT S.ID
, S.LASTNAME
, S.FIRSTNAME
FROM STUDENTS S
LEFT JOIN TEACHERS T
ON T.STUDENT_ID = S.ID
AND T.TEACHER_ID = $teacherID
WHERE T.TEACHER_ID is null
answered Feb 9 '16 at 19:06
paparazzopaparazzo
4,6141230
add a comment |
Your join should be on TEACHER_ID = STUDENTS.ID and not the ID of the TEACHERS table. Your WHERE clause should also use TEACHER_ID.
See if the below works for you.
SELECT S.ID
, S.LASTNAME
, S.FIRSTNAME
FROM STUDENTS S
LEFT OUTER JOIN TEACHERS T ON T.STUDENT_ID = S.ID
WHERE TEACHERS.TEACHER_ID <> $teacherID
edited Aug 22 '17 at 0:04
Jefferson Arrubla H
20718
answered Feb 9 '16 at 18:29
Jonathan FiteJonathan Fite
4,030818
add a comment |
OK let me guess that TEACHER table is what get's an INSERT once a teacher adds a student to themselves so the query should look more like this to fetch all the students not added yet by a given teacher with TEACHER_ID = $teacherID
SELECT S.ID
, S.LASTNAME
, S.FIRSTNAME
FROM STUDENTS S
HAVING S.ID NOT IN
(SELECT T.STUDENT_ID FROM TEACHERS T WHERE T.TEACHER_ID = '$teacherId' )
answered 22 mins ago
MarcelMarcel
1
New contributor
Marcel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f128754%2fhow-do-i-do-a-join-and-exclude-certain-records%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
A unique constraint on TEACHER_ID, STUDENT_ID would prevent duplicate STUDENT_ID for a teacher. In fact you could just make the the PK and drop ID.
This is TSQL so it may be wrong for mysql
SELECT S.ID
, S.LASTNAME
, S.FIRSTNAME
FROM STUDENTS S
LEFT JOIN TEACHERS T
ON T.STUDENT_ID = S.ID
AND T.TEACHER_ID = $teacherID
WHERE T.TEACHER_ID is null
answered Feb 9 '16 at 19:06
paparazzopaparazzo
4,6141230
add a comment |
A unique constraint on TEACHER_ID, STUDENT_ID would prevent duplicate STUDENT_ID for a teacher. In fact you could just make the the PK and drop ID.
This is TSQL so it may be wrong for mysql
SELECT S.ID
, S.LASTNAME
, S.FIRSTNAME
FROM STUDENTS S
LEFT JOIN TEACHERS T
ON T.STUDENT_ID = S.ID
AND T.TEACHER_ID = $teacherID
WHERE T.TEACHER_ID is null
answered Feb 9 '16 at 19:06
paparazzopaparazzo
4,6141230
add a comment |
A unique constraint on TEACHER_ID, STUDENT_ID would prevent duplicate STUDENT_ID for a teacher. In fact you could just make the the PK and drop ID.
This is TSQL so it may be wrong for mysql
SELECT S.ID
, S.LASTNAME
, S.FIRSTNAME
FROM STUDENTS S
LEFT JOIN TEACHERS T
ON T.STUDENT_ID = S.ID
AND T.TEACHER_ID = $teacherID
WHERE T.TEACHER_ID is null
answered Feb 9 '16 at 19:06
paparazzopaparazzo
4,6141230
A unique constraint on TEACHER_ID, STUDENT_ID would prevent duplicate STUDENT_ID for a teacher. In fact you could just make the the PK and drop ID.
This is TSQL so it may be wrong for mysql
SELECT S.ID
, S.LASTNAME
, S.FIRSTNAME
FROM STUDENTS S
LEFT JOIN TEACHERS T
ON T.STUDENT_ID = S.ID
AND T.TEACHER_ID = $teacherID
WHERE T.TEACHER_ID is null
answered Feb 9 '16 at 19:06
paparazzopaparazzo
4,6141230
answered Feb 9 '16 at 19:06
paparazzopaparazzo
4,6141230
answered Feb 9 '16 at 19:06
paparazzopaparazzo
4,6141230
answered Feb 9 '16 at 19:06
paparazzopaparazzo
4,6141230
4,6141230
add a comment |
add a comment |
Your join should be on TEACHER_ID = STUDENTS.ID and not the ID of the TEACHERS table. Your WHERE clause should also use TEACHER_ID.
See if the below works for you.
SELECT S.ID
, S.LASTNAME
, S.FIRSTNAME
FROM STUDENTS S
LEFT OUTER JOIN TEACHERS T ON T.STUDENT_ID = S.ID
WHERE TEACHERS.TEACHER_ID <> $teacherID
edited Aug 22 '17 at 0:04
Jefferson Arrubla H
20718
answered Feb 9 '16 at 18:29
Jonathan FiteJonathan Fite
4,030818
add a comment |
Your join should be on TEACHER_ID = STUDENTS.ID and not the ID of the TEACHERS table. Your WHERE clause should also use TEACHER_ID.
See if the below works for you.
SELECT S.ID
, S.LASTNAME
, S.FIRSTNAME
FROM STUDENTS S
LEFT OUTER JOIN TEACHERS T ON T.STUDENT_ID = S.ID
WHERE TEACHERS.TEACHER_ID <> $teacherID
edited Aug 22 '17 at 0:04
Jefferson Arrubla H
20718
answered Feb 9 '16 at 18:29
Jonathan FiteJonathan Fite
4,030818
add a comment |
Your join should be on TEACHER_ID = STUDENTS.ID and not the ID of the TEACHERS table. Your WHERE clause should also use TEACHER_ID.
See if the below works for you.
SELECT S.ID
, S.LASTNAME
, S.FIRSTNAME
FROM STUDENTS S
LEFT OUTER JOIN TEACHERS T ON T.STUDENT_ID = S.ID
WHERE TEACHERS.TEACHER_ID <> $teacherID
edited Aug 22 '17 at 0:04
Jefferson Arrubla H
20718
answered Feb 9 '16 at 18:29
Jonathan FiteJonathan Fite
4,030818
Your join should be on TEACHER_ID = STUDENTS.ID and not the ID of the TEACHERS table. Your WHERE clause should also use TEACHER_ID.
See if the below works for you.
SELECT S.ID
, S.LASTNAME
, S.FIRSTNAME
FROM STUDENTS S
LEFT OUTER JOIN TEACHERS T ON T.STUDENT_ID = S.ID
WHERE TEACHERS.TEACHER_ID <> $teacherID
edited Aug 22 '17 at 0:04
Jefferson Arrubla H
20718
answered Feb 9 '16 at 18:29
Jonathan FiteJonathan Fite
4,030818
edited Aug 22 '17 at 0:04
Jefferson Arrubla H
20718
edited Aug 22 '17 at 0:04
Jefferson Arrubla H
20718
edited Aug 22 '17 at 0:04
Jefferson Arrubla H
20718
20718
answered Feb 9 '16 at 18:29
Jonathan FiteJonathan Fite
4,030818
answered Feb 9 '16 at 18:29
Jonathan FiteJonathan Fite
4,030818
answered Feb 9 '16 at 18:29
Jonathan FiteJonathan Fite
4,030818
4,030818
add a comment |
add a comment |
OK let me guess that TEACHER table is what get's an INSERT once a teacher adds a student to themselves so the query should look more like this to fetch all the students not added yet by a given teacher with TEACHER_ID = $teacherID
SELECT S.ID
, S.LASTNAME
, S.FIRSTNAME
FROM STUDENTS S
HAVING S.ID NOT IN
(SELECT T.STUDENT_ID FROM TEACHERS T WHERE T.TEACHER_ID = '$teacherId' )
answered 22 mins ago
MarcelMarcel
1
New contributor
Marcel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
OK let me guess that TEACHER table is what get's an INSERT once a teacher adds a student to themselves so the query should look more like this to fetch all the students not added yet by a given teacher with TEACHER_ID = $teacherID
SELECT S.ID
, S.LASTNAME
, S.FIRSTNAME
FROM STUDENTS S
HAVING S.ID NOT IN
(SELECT T.STUDENT_ID FROM TEACHERS T WHERE T.TEACHER_ID = '$teacherId' )
answered 22 mins ago
MarcelMarcel
1
New contributor
Marcel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
OK let me guess that TEACHER table is what get's an INSERT once a teacher adds a student to themselves so the query should look more like this to fetch all the students not added yet by a given teacher with TEACHER_ID = $teacherID
SELECT S.ID
, S.LASTNAME
, S.FIRSTNAME
FROM STUDENTS S
HAVING S.ID NOT IN
(SELECT T.STUDENT_ID FROM TEACHERS T WHERE T.TEACHER_ID = '$teacherId' )
answered 22 mins ago
MarcelMarcel
1
New contributor
Marcel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
OK let me guess that TEACHER table is what get's an INSERT once a teacher adds a student to themselves so the query should look more like this to fetch all the students not added yet by a given teacher with TEACHER_ID = $teacherID
SELECT S.ID
, S.LASTNAME
, S.FIRSTNAME
FROM STUDENTS S
HAVING S.ID NOT IN
(SELECT T.STUDENT_ID FROM TEACHERS T WHERE T.TEACHER_ID = '$teacherId' )
answered 22 mins ago
MarcelMarcel
1
New contributor
Marcel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 22 mins ago
MarcelMarcel
1
New contributor
Marcel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 22 mins ago
MarcelMarcel
1
answered 22 mins ago
MarcelMarcel
1
1
New contributor
Marcel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Marcel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Marcel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f128754%2fhow-do-i-do-a-join-and-exclude-certain-records%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
