Again, I'm not sure what this will get you as I'm not an expert and don't
have the data or the knowledge of how SQL interperates your origional query,
with all the inner joins resulting from the where clause, but this should
get you closer if not there. I doubt that it will be the best practice.
[code]
$select = $db->select();
$select->from(array('s' => 'student'),
array());
$select->join(array('r' => 'registration'),
's.sid = r.sid',
array());
$select->join(array('sc' => 'student_course_interests'),
's.sid = sc.sid',
array());
$select->join(array('c' => 'course'),
'sc.cid = c.cid',
array('s.sid', 's.name', 'r.rid', 'r.rdate', 'c.name', 's.status'));
[/code]
or
[code]
$select = $db->select();
$select->from(array('s' => 'student'), array());
$select->from(array('r' => 'registration'), array());
$select->from(array('sc' => 'student_course_interests'), array());
$select->from(array('c' => 'course'),
array('s.sid', 's.name', 'r.rid', 'r.rdate', 'c.name', 's.status'));
$select->where('s.sid=r.sid');
$select->where('s.sid=sc.sid');
$select->where('c.cid=sc.cid');
[/code]
Jim
Sudheer Satyanarayana wrote:
>
> Hello experts,
>
> I am trying to implement a MySQL query with Zend_DB. The query is:
>
> 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
>
> When I execute this query in the MySQL client, I get the desired result.
> I tried various types of queries with ZF. I don't understand why Zend_DB
> changes the column positions. The below query doesn't seem to select
> s.name at all.
>
> [code]
>
> $select = $db->select();
> $select->from(array('s' => 'student'),
> array('s.sid','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'));
> $result = $db->fetchAll($select);
>
> [/code]
>
> How can I fix this?
>
> Any help is greatly appreciated.
>
> Regards,
> Sudheer
>
>
--
View this message in context:
http://www.nabble.com/Help-to-implement-a-join-query-in-Zend_Db-tp16506242p16516518.html
Sent from the Zend Framework mailing list archive at Nabble.com.
