How to fetch values from 2 tables based on the first table's values

Question

I have developed a custom module which includes three DB tables. I need to fetch values from one table and then based on that value fetch values from second table.

For ex below is the table description:

**deal_offers** : entity_id | deal_name | deal_discount

**deal_products** : deal_id | product_id

In my observer, I am getting product_id value. Now for say product_id, what I want to achieve is

1. First, fetch deal_id value from table deal_products, using the value of product_id

2. Next, fetch deal_discount value, using deal_id which is the entity_id in table deal_offers

I have defined <entities> in my config.xml for these two tables, but not getting success in achieving the desired result using Mage::getModel('module/model') method in my observer.

Please Help

Thanks Thanks

Answer 1

Try using following code snippet, it's an example of inner join operation of two table.

$collection = Mage::getModel('module/modelB')->getCollection()
    ->addFieldToFilter('product_id',$product_id);
$collection->getSelect()
    ->join('deal_offers', 'main_table.entity_id=deal_offers.deal_id',array('deal_name','deal_discount'));

Answer 2

Try using

$collection = Mage::getModel('module/modelB')->getCollection() ->addFieldToFilter('product_id',$product_id)->getFirstItem();