I am trying to build a SQL query with INTERSECT clause. I get a array of email ids and in return I need to give out the common students between all the teachers provided by the email ids in the input.
The structure of the table is:
id | teacher_email | student_email | valid |
41 | [email protected] | [email protected] | 1 |
42 | [email protected] | [email protected] | 1 |
43 | [email protected] | [email protected] | 1 |
44 | [email protected] | [email protected] | 1 |
58 | [email protected] | [email protected] | 1 |
59 | [email protected] | [email protected] | 1 |
60 | [email protected] | [email protected] | 1 |
61 | [email protected] | [email protected] | 1 |
62 | [email protected] | [email protected] | 1 |
63 | [email protected] | [email protected] | 1 |
64 | [email protected] | [email protected] | 1 |
65 | [email protected] | [email protected] | 1 |
66 | [email protected] | [email protected] | 1 |
67 | [email protected] | [email protected] | 1 |
69 | [email protected] | [email protected] | 1 |
70 | [email protected] | [email protected] | 1 |
This is the API code
router.get("/api/commonstudents", (req, res) => {
console.log("Trying to retrieve common students of given teachers...")
console.log("Teacher's email IDs: " + req.query.teacher)
const teacherEmailIDs = req.query.teacher
var commonStudentsForTeachersQueryString = "";
const connection = getConnection()
console.log("no of teachers: " + teacherEmailIDs)
teacherEmailIDs.forEach(function(teacherEmailID) {
console.log(teacherEmailID);
commonStudentsForTeachersQueryString += "select student_email from register where teacher_email=? and valid=1";
commonStudentsForTeachersQueryString += " INTERSECT "
})
commonStudentsForTeachersQueryString = commonStudentsForTeachersQueryString.substr(0, commonStudentsForTeachersQueryString.lastIndexOf(" INTERSECT "))
console.log("Query formed is :: " + commonStudentsForTeachersQueryString);
connection.query(commonStudentsForTeachersQueryString, teacherEmailIDs, (errCommonStudentTeacher, rowsCommonStudentTeacher) => {
if (errCommonStudentTeacher) {
console.log("Failed to query for users: " + errCommonStudentTeacher)
console.error(errCommonStudentTeacher);
res.status(500).json({"message": "Some Internal Error Occured"})
return
}
if (rowsCommonStudentTeacher === undefined || rowsCommonStudentTeacher.length == 0) {
//rows empty or does not exist
console.log("No common students")
res.status(404).json({"message": "No Common Students found"})
}
else{
//common students found
console.log("Common students Found" + rowsCommonStudentTeacher.length)
const commonStudents = rowsCommonStudentTeacher.map((commonStudent)=>{
return commonStudent.student_email
})
res.status(200).json({"students": commonStudents})
}
})
})
Here is what console prints:
Harshs-MacBook-Air:nodejs_api harshvardhan$ node app.js
Server is up and listening on 3003...
Trying to retrieve common students of given teachers...
Teacher's email IDs: [email protected],[email protected],[email protected]
no of teachers: [email protected],[email protected],[email protected]
[email protected]
[email protected]
[email protected]
Query formed is :: select student_email from register where teacher_email=? and valid=1 INTERSECT select student_email from register where teacher_email=? and valid=1 INTERSECT select student_email from register where teacher_email=? and valid=1
Failed to query for users: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTERSECT select student_email from register where teacher_email='[email protected]' at line 1
{ Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTERSECT select student_email from register where teacher_email='[email protected]' at line 1
at Query.Sequence._packetToError (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
at Query.ErrorPacket (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/mysql/lib/protocol/sequences/Query.js:77:18)
at Protocol._parsePacket (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/mysql/lib/protocol/Protocol.js:291:23)
at Parser._parsePacket (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/mysql/lib/protocol/Parser.js:433:10)
at Parser.write (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/mysql/lib/protocol/Parser.js:43:10)
at Protocol.write (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/mysql/lib/protocol/Protocol.js:38:16)
at Socket.<anonymous> (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/mysql/lib/Connection.js:91:28)
at Socket.<anonymous> (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/mysql/lib/Connection.js:525:10)
at emitOne (events.js:116:13)
at Socket.emit (events.js:211:7)
--------------------
at Pool.query (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/mysql/lib/Pool.js:199:23)
at router.get (/Users/harshvardhan/Documents/work/test/nodejs_api/routes/admin.js:129:14)
at Layer.handle [as handle_request] (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/express/lib/router/layer.js:95:5)
at next (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/express/lib/router/route.js:137:13)
at Route.dispatch (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/express/lib/router/route.js:112:3)
at Layer.handle [as handle_request] (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/express/lib/router/layer.js:95:5)
at /Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/express/lib/router/index.js:281:22
at Function.process_params (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/express/lib/router/index.js:335:12)
at next (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/express/lib/router/index.js:275:10)
at Function.handle (/Users/harshvardhan/Documents/work/test/nodejs_api/node_modules/express/lib/router/index.js:174:3)
code: 'ER_PARSE_ERROR',
errno: 1064,
sqlMessage: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'INTERSECT select student_email from register where teacher_email=\'[email protected]\' at line 1',
sqlState: '42000',
index: 0,
sql: 'select student_email from register where teacher_email=\'[email protected]\' and valid=1 INTERSECT select student_email from register where teacher_email=\'[email protected]\' and valid=1 INTERSECT select student_email from register where teacher_email=\'[email protected]\' and valid=1' }
::ffff:127.0.0.1 - GET /api/[email protected]&[email protected]&[email protected] HTTP/1.1 500 41 - 65.808 ms
This query works well when I use it as is.
select student_email from register where teacher_email='[email protected]' and valid=1
But when used with INTERSECT with other queries it gives an aforementioned error.
How can this issue be resolved, I believe this has something to do with email format and its character's encoding. I have scavenged forums and tried few things to reach till here. Thanks for any pointers you can give towards right approach.
Here is the GitHub link for reference.
there is not INTERSECT in mysql ..
if you really need an intersect you could use several inner join
select student_email
from register r
INNER JOIN (
select student_email from
register where teacher_email=? and valid=1
) t1 ON t1.student_email = r.student_email
INNER JOIN (
INTERSECT
INTERSECT select student_email from register
where teacher_email=? and valid=1
) t2 ON t2.student_email = t1.student_email
where r.teacher_email=? and r.valid=1