Magento join only selected table column, avoid ambiguous

Question

I am trying to join two tables and then filtering it by product_id...

The problem is that both tables have the same column and I am getting following error

Integrity constraint violation: 1052 Column 'product_id' in on clause is ambiguous, query was: SELECT COUNT(*) FROM `booking_ticket` AS `main_table`
 INNER JOIN `sales_flat_order_item` AS `order_item` ON order_item_id=order_item.item_id
 INNER JOIN `sales_flat_order` AS `order` ON order_item.order_id=order.entity_id
 LEFT JOIN `marketplace_commission` AS `marketplace_data` order.entity_id=marketplace_data.order_id WHERE (`product_id` IN('189'))

I am joining like this

$this->getSelect()
            // ->reset(Zend_Db_Select::COLUMNS)
            // ->columns()
            ->joinLeft( // Product ID, Order ID
                array('marketplace_data' => $this->getTable('marketplace/commission')),
                'order.entity_id=marketplace_data.order_id',
                array(
                  "origin" => "marketplace_data.origin"
                )
            );

and then filtering like this

$ticketCodes = Mage::getModel('booking/ticket')->getCollection()
      ->joinOrderData()->joinMarketPlaceData()
      ->addFieldToFilter('product_id',array('in'=> $id));

I need to join my marketplace table without joining the product_id column because the order table already have product id. How can I achieve this? I have tried resetting as it is commented above. No effect.

Thank you in advance

Answer 1

You need fully qualified column name for product_id column (assuming the product_id is in order_item table)

SELECT COUNT(*) FROM `booking_ticket` AS `main_table`
INNER JOIN `sales_flat_order_item` AS `order_item` ON order_item_id=order_item.item_id
INNER JOIN `sales_flat_order` AS `order` ON order_item.order_id=order.entity_id
LEFT JOIN `marketplace_commission` AS `marketplace_data` 
  ON `order_item`.product_id=marketplace_data.product_id
WHERE (`order_item`.`product_id` IN('189'))