ok i have this query which is working that select data from 3 tables
SELECT user_id, matric_no, course_id, name, email, supervisor, project_id, status FROM (jos_main_students LEFT JOIN jos_users ON user_id = jos_users.id) LEFT JOIN jos_main_allocations ON student_id = jos_users.id;
However course_id is the PK of another table called _main_course and id like to select a field called title from _main_course and have the title of the course display in place of just its course_id.
mysql Inner joins or sub-querys, syntax help needed
Moderator: General Moderators
Re: mysql Inner joins or sub-querys, syntax help needed
Code: Select all
SELECT s.user_id
, s.matric_no
, s.course_id
, c.title
, u1.name student_name
, u1.email student_email
, a.supervisor supervisor_id
, u2.name supervisor_name
, a.project_id
, a.status
FROM jos_main_students s
LEFT
JOIN jos_users u1
ON u1.id = s.user_id
LEFT
JOIN jos_main_allocations a
ON a.student_id = u.id
JOIN _main_course c
ON c.id = s.course_id
JOIN jos_users u2
ON u2.id = a.supervisor;