Select information across 2 different tables

Question

I have 2 MySQL tables. One contains info on students, the other on teachers.

In order to find either the student or teacher ID number, I must look it up based on the email address provided.

However I have no way of knowing whether or not the email is a student or teacher ID.

How can I query both tables, so that looking up [email protected] will return the ID number no matter if John Doe is a teacher or a student?

If it helps, in the student table, the student emails are in a column called "Person_Email", and the IDs are in a column called "StudentID". In the teachers table, the teacher emails are in a column called "Teacher_Email", and the IDs are in a column called "TeacherID".

Answer 1

If a given email cannot exists in both tables, you can just union all two queries like follows:

select StudentID id from student where Person_Email = :email
union all select TeacherID from teacher where Teacher_Email = :email

If you want to know from which table the record is coming from, you can add one column in the resultset:

select StudentID id 'student' tbl from student where Person_Email = :email
union all select TeacherID, 'teacher' from teacher where Teacher_Email = :email