Page 1 of 1

mysql Inner joins or sub-querys, syntax help needed

Posted: Thu Apr 15, 2010 5:39 pm
by recci
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.

Re: mysql Inner joins or sub-querys, syntax help needed

Posted: Thu Apr 15, 2010 7:49 pm
by recci

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;