Join the two tables in Magento 2

Question

I'm trying to join two tables but it's returning wrong result could you please suggest me how to write it.

Data: From my custom table(product_id,email,id) have Product_id.

Expecting Result : join the two tables (custom_table and catalog_product_entity) to display the Product Name, Sku and Email_id

Could you please tell me how to write it?

Answer 1

This works for me. The only change between Magento 1 and Magento 2 is how to get the table name:

$collection->getSelect()->joinLeft(
   ['admin'=>$collection->getTable('admin_user')],
   'main_table.admin_user_id = admin.user_id',
   ['admin_username'=>'admin.username']);

Answer 2

I will do something like this:

    $connection = $this->_objectManager->create('\Magento\Framework\App\ResourceConnection');
    $conn = $connection->getConnection();
    $select = $conn->select()
        ->from(
            ['main_table' => 'catalog_product_entity'],
            [
                'main_table.sku',
                'custom_table.email_id'
            ]
        )
        ->join(
            ['custom_table' => 'custom_table'],
            'main_table.entity_id = custom_table.product_id'
        );

    $data = $conn->fetchAll($select);