Getting ER_PARSE_ERROR when using Intersect and multiple input vaules of email format

Question

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 | Tom@gmail.com    | May@gmail.com       |     1 |
42 | Tom@gmail.com    | jerry@gmail.com     |     1 |
43 | Tom@gmail.com    | Kestov@gmail.com    |     1 |
44 | Toni@gmail.com   | hdjh@kd.com         |     1 |
58 | larry@gmail.com  | jerry@gmail.com     |     1 |
59 | gerrad@gmail.com | jerry@gmail.com     |     1 |
60 | gerrad@gmail.com | Kestov@gmail.com    |     1 |
61 | gerrad@gmail.com | Katrin@gmail.com    |     1 |
62 | gerrad@gmail.com | Piniyara@gniail.com |     1 |
63 | gerrad@gmail.com | Ritz@gmail.com      |     1 |
64 | gerrad@gmail.com | Taz@gmail.com       |     1 |
65 | gerrad@gmail.com | Fensuk@gmail.com    |     1 |
66 | gerrad@gmail.com | Joe@gmail.com       |     1 |
67 | gerrad@gmail.com | Mustafa@gmail.com   |     1 |
69 | Tom@gmail.com    | Fensuk@gmail.com    |     1 |
70 | Tom@gmail.com    | Taz@gmail.com       |     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: Tom@gmail.com,gerrad@gmail.com,larry@gmail.com
no of teachers: Tom@gmail.com,gerrad@gmail.com,larry@gmail.com
Tom@gmail.com
gerrad@gmail.com
larry@gmail.com
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='gerrad@gmail.c' 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='gerrad@gmail.c' 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=\'gerrad@gmail.c\' at line 1',
  sqlState: '42000',
  index: 0,
  sql: 'select student_email from register where teacher_email=\'Tom@gmail.com\' and valid=1 INTERSECT select student_email from register where teacher_email=\'gerrad@gmail.com\' and valid=1 INTERSECT select student_email from register where teacher_email=\'larry@gmail.com\' and valid=1' }
::ffff:127.0.0.1 - GET /api/commonstudents?teacher=Tom@gmail.com&teacher=gerrad@gmail.com&teacher=larry@gmail.com 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='Tom@gmail.com' 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.

score 0 · Accepted Answer · answered Aug 16 '19 at 08:21

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

thanks for sharing that. I will resort back to Self Join then. — Harsh Vardhan, Aug 16 '19 at 09:19

Getting ER_PARSE_ERROR when using Intersect and multiple input vaules of email format

1 Answers1