Display data from multiple database tables to frontend

Question

How to get data from 2 database tables and show them in the custom frontend page? I have been looking for the tutorials, so far what i get is creating SQL Left Join in the Collection.php (even though i do not know whether this is working or not)

<?php
namespace Namespace\Modulename\Model\Resource\Modulename;

use Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection;

class Collection extends AbstractCollection
{
    /**
     * Define model & resource model
     */
    const YOUR_TABLE = 'tablename';

    public function __construct(
        \Magento\Framework\Data\Collection\EntityFactoryInterface $entityFactory,
        \Psr\Log\LoggerInterface $logger,
        \Magento\Framework\Data\Collection\Db\FetchStrategyInterface $fetchStrategy,
        \Magento\Framework\Event\ManagerInterface $eventManager,
        \Magento\Store\Model\StoreManagerInterface $storeManager,
        \Magento\Framework\DB\Adapter\AdapterInterface $connection = null,
        \Magento\Framework\Model\ResourceModel\Db\AbstractDb $resource = null
    ) {
        $this->_init(
            'Namespace\Modulename\Model\Modulename',
            'Namespace\Modulename\Model\Resource\Modulename'
        );
        parent::__construct(
            $entityFactory, $logger, $fetchStrategy, $eventManager, $connection,
            $resource
        );
        $this->storeManager = $storeManager;
    }
    protected function _initSelect()
    {
        parent::_initSelect();

        $this->getSelect()->joinLeft(
                ['secondTable' => $this->getTable('tablename')],
                'main_table.columnname = secondTable.columnname',
                ['columnname1','columnname2','columnname3']
            );
    }
}
?>

Answer 1

here is a sample for join to product collection

protected $productCollectionFactory;


public function __construct(
        .....
        \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $productCollectionFactory,
        ...
    ) {
        $this->productCollectionFactory = $productCollectionFactory;
       ....
    }

   public function getCollection()
   {
    $collection = $this->productCollectionFactory->create();
            $collection->getSelect()->joinLeft(
                ['productsold'=>$collection->getTable('productsold')],
                'e.entity_id = productsold.product_id',
                ['offsite'=>'productsold.off_sold','soldlabel'=>'productsold.label','soldid'=>'productsold.id']);
    return $collection;   
    }