将Mysql查询转换为Magento查询

Actually this post is for two questions since the first question will lead to the second question.

I have a Mysql Query in which I need to select 2 tables. First table is 1 row(main table) and the second table has multiple rows(joining table).

With only parent_id and entity_id connecting them to achieve the result, I created a query in which it concats the column results of the 2nd table into 1 row only and then join it to the first table.

Here's the query.

SELECT `main_table`.*, 
(SELECT GROUP_CONCAT(comment SEPARATOR '----' ) 
FROM `mgmx_sales_flat_invoice_comment` a WHERE a.parent_id = main_table.entity_id 
group by a.parent_id)
FROM `mgmx_sales_flat_invoice_grid` AS `main_table` 
INNER JOIN `mgmx_sales_flat_invoice_comment` AS `a` 
ON a.parent_id = main_table.entity_id

1st Question is: It shows the result I want but it returned 2 rows with similar data like the sample below. How do I eliminate the second record.

2nd Question: How to you convert this into Zend_Framework/Magento $collection query.

So far I have this Query but I need to apply the query above.

protected function _prepareCollection()
{
    $collection = Mage::getResourceModel($this->_getCollectionClass());
    $collection->getSelect()->join( array('a'=> mgmx_sales_flat_invoice_comment), 'a.parent_id = main_table.entity_id', array('a.comment'));
    $this->setCollection($collection);
    return parent::_prepareCollection();
}

I am not familiar with Magneto and it doesn't look trivial, so I won't attempt that.

However, if you moved that "correlated subquery" out of the select clause and into the from clause as a subquery, it may be simpler to translate. To be honest it is probably also more efficient this way too.

SELECT `main_table`.* , c.comments
FROM   `mgmx_sales_flat_invoice_grid` AS `main_table` 
INNER JOIN `mgmx_sales_flat_invoice_comment` AS `a` ON a.parent_id = main_table.entity_id 
LEFT JOIN (
          SELECT a.parent_id, Group_concat(comment SEPARATOR '----') as comments
          FROM   `mgmx_sales_flat_invoice_comment` a 
          GROUP  BY a.parent_id
          ) c ON  a.parent_id = main_table.entity_id 

----

This former question show how to LEFT JOIN a subquery in Magneto Using an expression as a join.

Applying that logic to my suggestion, it looks something like this (it seems)

$query = 'SELECT parent_id, Group_concat(comment SEPARATOR ', ') as comments FROM `mgmx_sales_flat_invoice_comment` GROUP  BY parent_id';
$collection->getSelect()->joinLeft(
    new Zend_Db_Expr('('.$query.')'),
    'main_table.entity_id = t.parent_id',
    array('author_debit_amount')
);

but I do not understand where the alias t comes from (it is used in the previously cited question)