php - using left join on mutiple tables with conditional statement -

i have problem mysql can solved using inner join not with, have 4 tables.

table 1: classrooms classroomid, name, .......

table 2: teachers fname, mname, lname, .......

table 3: subjects subjectid, name, classroom_id, .....

table 4 teachers_subjects teacher_id , subject_id

i want query me class names (classrooms.name) , subjects name (subjects.name) subjects.classroom_id = classrooms.classroomid

i want names of teachers (teachers.name) teachers_subjects.subject_id = subjects.subjectid , teachers_subjects.teacher_id= teachers.teacherid.

i using query :

select classrooms.name, subjects.name,  concat(teachers.fname,' ',teachers.mname,' ',teachers.lname) teachers_name   classrooms, subjects, teachers, teachers_subjects  subjects.classroom_id=classrooms.classroomid  ,  teachers_subjects.teacher_id=teachers.teacherid  , teachers_subjects.subject_id=subjects.subjectid 

i want want classes , corresponding subjects if not on table teachers_subjects. list classes , subjects if subject not assigned teacher.

thanks

you try like

select  classrooms.name,      subjects.name,      concat(teachers.fname,' ',teachers.mname,' ',teachers.lname) teachers_name      classrooms inner join     subjects on subjects.classroom_id=classrooms.classroomid  left join      teachers_subjects on teachers_subjects.subject_id=subjects.subjectid left join     teachers on teachers_subjects.teacher_id=teachers.teacherid  

have @ article (introduction joins – basic of joins) nice graphical introduction.