database - Mysql join query help to show 2 seperate records -

i have query returns courses has status = "offering" , offered user has course courseid "3" problem

there sc_c_material table has 2 records against course want show 2 seperate rows against this, 1 row has sc_course data 1st record of sc_c_material , 2nd row again sc_course data 2nd record of sc_c_material

the below query only return 1 record course has id 9 id 9 has 2 records in sc_c_materials table. want both of record sc_c_material table

i hope have cleared point, because bit hard explain

select c.`id`,      c.`coursecode`,      m.`title`,      a.`author_name`,      q.`quality`,      m.`comments`,      s.status,      u.id,      m.`material` sc_courses c,      sc_status s,      sc_c_materials m,       sc_authors a,      sc_quality q,      users u c.`statusid`=s.`id`     , c.`userid`=u.`id`     , m.`qualityid`=q.`id`     , m.`authorid`=a.`id`     , c.`id`=m.`courseid`     , s.`status`="offering"     , c.`userid` in (select userid                  sc_courses                  id="3"                  group userid) 

first, start using ansi sql-92 style queries show join relations instead of bulking them in clause. gives better visualization on how table x related table y (although sql-89 format still works). also, should need tic ` marks around fields might cause problem due reserved words column names.

the underlying query should work, however, looking @ join conditions, seeing materials table joined both authors , quality tables. if there missing / invalid id relationship either of tables, 1 record returned. in case, have changed left-join see if case

select        c.id,       c.coursecode,       m.`title`,       a.author_name,       q.quality,       m.comments,       s.status,       u.id,       m.material           sc_courses c          join sc_status s             on c.statusid = s.id            , s.status= 'offering'           join sc_c_materials m             on c.id= m.courseid              left join sc_authors                on m.authorid = a.id             left join sc_quality q                on m.qualityid = q.id           join users u             on c.userid = u.id          c.userid in (select userid                  sc_courses                  id='3'                 group userid) 

the left-join means want left table listed first within table clause... in case sc_c_materials (in relation authors , quality) regardless of finding match in authors or quality tables. so, during such not-found elements in other tables, columns may attempt pull them come null.

since had implied inner join (all criteria in clause directly), query telling engine... want records exist pieces found, ignoring record did not have match in whichever table missing link.