Select only one table in a join query

Question

I have this working SQL query :

SELECT main_table.* FROM prd_brand AS main_table INNER JOIN catalog_product_entity_int ON main_table.brand_id=catalog_product_entity_int.value group by brand_id order by name asc

And I'm trying to get it work in Magento, so I did this :

        $brandCollection = $brand->getCollection();
        $brandCollection->join('catalog_product_entity_int', 'catalog_product_entity_int.value = main_table.brand_id', '*');
        echo $brandCollection->getSelect();

It generates this SQL query:

SELECT `main_table`.*, `catalog_product_entity_int`.* FROM `prd_brand` AS `main_table` INNER JOIN `catalog_product_entity_int` ON catalog_product_entity_int.value = main_table.brand_id

The problem is that I don't like to select the catalog_product_entity_int but only the main_table (prd_brand) table.

How can I do it ?

Answer 1

can you please try like this it will work

$brandCollection = $brand->getCollection();
$brandCollection->join(
    array(
        'cpei' => 'catalog_product_entity_int'),
        'cpei.value = main_table.brand_id',
        array()
    )

Group by use :

->group ( 'main_table.brand_id' )

Order by use :

->order(array('line_items_per_product DESC','product_id'));