This is the problem I am having, Tables and fields
member - memberID, forenames, memberType(lecturer, assistant)
course - courseID, lecturerID, courseName
class - classID, assistantID, courseID
*lecturerID in course is a foreign key to memberID of member and assistantID in class is a foreign key to memberID of member as the member table keeps details about both course lecturers and class assistants.
Basically tables are connected in this manner,
FROM member, course, class
WHERE class.assistantID = member.memberID
AND class.courseID = course.courseID
AND course.lecturerID = member.memberID
How can I display lecturer, assistant, courseName in different columns filtered by lecturer forenames and/or assistant forenames?
If I say lecturer name is LIKE 'John%', it shouldn't filter by assistant name.
It will save me a lot of time if I could achieve this without changing the table structure.