select s.sid, s.name, r.rid, r.rdate, c.name, s.status from student as s, registration as r, student_course_interests as sc, course as c where s.sid=r.sid and s.sid=sc.sid and c.cid=sc.cid;

//Maybe this is it?

$select = $this->select();

$select->from(array('s' => 'student'),
              array('s.id','s.name','s.status'));
$select->join(array('r' => 'registration'),
              's.sid = r.sid',
              array('r.rid','r.rdate'));
$select->join(array('sc' => 'student_course_interests'),
              's.sid = sc.sid');
$select->join(array('c' => 'course'),
              'c.cid = sc.cid',
              array('c.name'));
$results = $this->getDefaultAdapter()->fetchAll($select);              

Zend_Debug::dump($results);