mysql Inner joins or sub-querys, syntax help needed

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
recci
Forum Commoner
Posts: 42
Joined: Tue Jul 29, 2008 10:01 pm

mysql Inner joins or sub-querys, syntax help needed

Post 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.
recci
Forum Commoner
Posts: 42
Joined: Tue Jul 29, 2008 10:01 pm

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

Post 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;
Post Reply