Stack Exchange
How to join two custom table in Magento 2.3
I have two tables as below
pscustom_catalog_product_entity

pscustom_catalog_product_entity_media

I have created collection for table pscustom_catalog_product_entity using resource model and block.
Now I can access this table data using collection.
app/code/MyModule/Productsinfo/Model/Product.php
<?php

namespace MyModule\Productsinfo\Model;

use Magento\Framework\Model\AbstractModel;
use MyModule\Productsinfo\Model\ResourceModel\Product as ResourceModel;

class Product extends AbstractModel
{
    protected function _construct()
    {
        $this->_init(ResourceModel::class);
    }
}    

app/code/MyModule/Productsinfo/Model/ResourceModel/Product.php
<?php

namespace MyModule\Productsinfo\Model\ResourceModel;

use Magento\Framework\Model\ResourceModel\Db\AbstractDb;

class Product extends AbstractDb
{
    protected function _construct()
    {
        $this->_init('pscustom_catalog_product_entity', 'entity_id');
    }
}

app/code/MyModule/Productsinfo/Model/ResourceModel/Product/Collection.php
<?php
namespace MyModule\Productsinfo\Model\ResourceModel\Product;

use Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection;
use MyModule\Productsinfo\Model\Product as Model;
use MyModule\Productsinfo\Model\ResourceModel\Product as ResourceModel;

class Collection extends AbstractCollection
{
    protected function _construct()
    {
        $this->_init(Model::class, ResourceModel::class);
    }
}

app/code/MyModule/Productsinfo/Block/Product.php
<?php 

namespace MyModule\Productsinfo\Block;

use Magento\Framework\View\Element\Template;
use MyModule\Productsinfo\Model\ResourceModel\Product\Collection;

class Product extends Template
{
    /**
     * @var Collection
     */
    private $collection;

    /**
     * Hello constructor.
     * @param Template\Context $context
     * @param Collection $collection
     * @param array $data
     */
    public function __construct(
        Template\Context $context,
        Collection $collection,
        array $data = []
    )
    {
        parent::__construct($context, $data);
        $this->collection = $collection;
    }

    public function getAllProducts() {
        return $this->collection;
    }

I can get product collection using getAllProducts() method.
Now I want to join table pscustom_catalog_product_entity collection to table pscustom_catalog_product_entity_media.  I have tried many solution but its not working.
Any help would be appreciated.
Thanks.

I think you can join tables in following way:
 $this->getSelect()->join(
            ['secondTable'=>$this->getTable('pscustom_catalog_product_entity')],
            'main_table.id = secondTable.entity_id','*');
      $this->getSelect()->join(
            ['thirdTable'=>$this->getTable('pscustom_catalog_product_entity_media')],
            'main_table.id = thirdTable.entity_id', '*');

      }

This is the simplest way of joining two tables in collection
