I'm getting some problems turning the SQL below right into a Zend Db query.

$choose = ' Choose s.id, i.id as instance_id, i.reference, i.title, i.sic_code, i.start_date

FROM sles s

JOIN sle_instances i

ON s.id = i.sle_id

WHERE i.id = (Choose MAX(id)

FROM sle_instances

WHERE sle_id = s.id

)

ORDER BY i.title ASC'

I've got so far as the code before - but Zend Db is not creating the query properly. Can anyone show me things i missing??

$choose = $db->select() ->from('sles', array(   'id',

                                                'instance_id'   => 'sle_instances.id',

                                                'reference'     => 'sle_instances.reference',

                                                'name'          => 'sle_instances.name',

                                                'sic_code'      => 'sle_instances.sic_code',

                                                'start_date'    => 'sle_instances.start_date'

                                             )

                              )

                        ->join('sle_instances', 'sles.id = sle_instances.sle_id')

                        ->where('sles.id = (Choose MAX(id) FROM sle_instances WHERE sle_id = sles.id)')

                        ->order('sle_instances.title ASC')

The SQL works incidentally. I'm spinning it using Zend Db as If only to make use of the Zend Paginator functionality.

Any assistance is greatly appreciated.

PJ

This:

    $choose = $db->select()->from(array("s" => "sles"), array("s.id","i.id as instanceid","i.reference","i.title","i.sic_code","i.start_date"))

                                 ->join(array('i' => "sle_instances"),"s.id = i.sle_id",array())

                                 ->where("i.id = (choose max(id) from sle_instances where sle_id = s.id)")

                                 ->order('i.title asc')

Gives this:

"Choose `s`.`id`, `i`.`id` AS `instanceid`, `i`.`reference`, `i`.`name`, `i`.`sic_code`, `i`.`start_date` FROM `sles` AS `s`

 INNER JOIN `sle_instances` AS `i` ON s.id = i.sle_id WHERE (i.id = (choose max(id) from sle_instances where sle_id = s.id)) ORDER BY `i`.`name` asc"

if you would like, you are able to take what @karim79 did and turn your subselect right into a $this->select() too...

$subselect = $db->select()

->from('sle_instances', array(new Zend_Db_Expr('max(id)')))

->where('sle_id = s.id')

$choose = $db->select()->from(array("s" => "sles"),

array("s.id","i.id as instanceid","i.reference","i.title","i.sic_code","i.start_date"))

->join(array('i' => "sle_instances"),"s.id = i.sle_id",array())

->where("i.id = ($subselect)")

->order('i.title asc')

print($choose)

//Choose `s`.`id`, `i`.`id` AS `instanceid`, `i`.`reference`, `i`.`name`, `i`.`sic_code`, `i`.`start_date` FROM `sles` AS `s` INNER JOIN `sle_instances` AS `i` ON s.id = i.sle_id WHERE (i.id = (Choose max(id) FROM `sle_instances` WHERE (sle_id = s.id))) ORDER BY `i`.`name` asc

I'd a really similar problem and that i discovered that this question can be simply written the following:

$choose = $db->select()

  ->from (

    array("s" => "sles"),

    array(

      "s.id",

      "instanceid" => "i.id",

      "i.reference",

      "i.title",

      "i.sic_code",

      "i.start_date")

  )

  ->join(

    array('i' => "sle_instances"),

    "s.id = i.sle_id",

    array()

  )

  ->where ("i.id = (" .

    $db->select()

    ->from('sle_instances', array(new Zend_Db_Expr('max(id)')))

    ->where('sle_id = s.id')

  .")")

  ->order('i.title asc')

print($choose)

It's exactly like people has mentioned here. However I felt it's a little simpler to see because the sub-query dependencies tend to be more apparent.