Creating a fully functional grid using the data from your database table in the Magento admin is no easy task. In fact, it can become more complicated when you need to join two tables using a UI grid component in Magento 2.

If you added a grid component using the step by step guide, you’ve probably defined a <virtualType> grid collection class. The configuration for this within the module’s di.xml file might look like the following.

<virtualType name="Thecoachsmb\Grid\Model\ResourceModel\Grid\Grid\Collection" type="Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult">
        <arguments>
            <argument name="mainTable" xsi:type="string">thecoachsmb_article_grid</argument>
            <argument name="resourceModel" xsi:type="string">Thecoachsmb\Grid\Model\ResourceModel\Grid</argument>
        </arguments>
    </virtualType>

In order to join multiple tables, the <virtualType> needs to be changed to a <type> and contain a few more arguments. For join you need to edit following files app/code/NameSpace/ModuleName/etc/di.xml In my case, it is app/code/Thecoachsmb/Grid/etc/di.xml 

• • For join we need to update di.xml file as following

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <!-- here we remove virtualType and defile collection as follow-->
    <type name="Thecoachsmb\Grid\Model\ResourceModel\Grid\Grid\Collection">
        <arguments>
            <argument name="mainTable" xsi:type="string">thecoachsmb_article_grid</argument>
            <argument name="eventPrefix" xsi:type="string">thecoachsmb_article_grid_grid_collection</argument>
            <argument name="eventObject" xsi:type="string">thecoachsmb_article_grid_collection</argument>
            <argument name="resourceModel" xsi:type="string">Thecoachsmb\Grid\Model\ResourceModel\Grid</argument>
        </arguments>
    </type>
    <type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
        <arguments>
            <argument name="collections" xsi:type="array">
                <!--data provider name which used in grid ui component file -->
                <item name="thecoachsmb_grid_listing_data_source" xsi:type="string">Thecoachsmb\Grid\Model\ResourceModel\Grid\Grid\Collection</item>
            </argument>
        </arguments>
    </type>
</config>

2# Then you have to update app/code/NameSpace/ModuleName/Model/ResourceModel/ModelName/Collection.php folder structure 

In my case it is app/code/Thecoachsmb/Grid/Model/ResourceModel/Grid/Collection.php

<?php
namespace Thecoachsmb\Grid\Model\ResourceModel\Grid;

/* use required classes */
use Magento\Framework\Data\Collection\EntityFactoryInterface;
use Psr\Log\LoggerInterface;
use Magento\Framework\Data\Collection\Db\FetchStrategyInterface;
use Magento\Framework\Event\ManagerInterface;
use Magento\Store\Model\StoreManagerInterface;
use Magento\Framework\DB\Adapter\AdapterInterface;
use Magento\Framework\Model\ResourceModel\Db\AbstractDb;

class Collection extends \Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection
{
    /**
     * @var string
     */
    protected $_idFieldName = 'article_id'; //primary id of the table

    /**
     * @param EntityFactoryInterface $entityFactory,
     * @param LoggerInterface        $logger,
     * @param FetchStrategyInterface $fetchStrategy,
     * @param ManagerInterface       $eventManager,
     * @param StoreManagerInterface  $storeManager,
     * @param AdapterInterface       $connection,
     * @param AbstractDb             $resource
     */
    public function __construct(
        EntityFactoryInterface $entityFactory,
        LoggerInterface $logger,
        FetchStrategyInterface $fetchStrategy,
        ManagerInterface $eventManager,
        StoreManagerInterface $storeManager,
        AdapterInterface $connection = null,
        AbstractDb $resource = null
    ) {
        $this->_init('Thecoachsmb\Grid\Model\Grid', 'Thecoachsmb\Grid\Model\ResourceModel\Grid');
        
        parent::__construct($entityFactory, $logger, $fetchStrategy, $eventManager, $connection, $resource);
        $this->storeManager = $storeManager;
    }
    
    protected function _initSelect()
    {
        parent::_initSelect();

        $this->getSelect()->joinLeft(
            ['secondTable' => $this->getTable('sales_order_grid')], //2nd table name by which you want to join mail table
            '`main_table`.id= `secondTable`.entity_id', // common column which available in both table 
            ['secondTable.customer_email','secondTable.customer_name'] // '*' define that you want all column of 2nd table. if you want some particular column then you can define as ['column1','column2']
        );
    }
}

Heading back to the _initSelect() method in the collection class, you can write the following code to join the sales_order_grid table.

Parameters:

1. Tablename
2. Conditions
3. columns you want to select

Join 3 tables and More

Use thirdTable, fourthTable to join more tables like below:

Examples :

protected function _initSelect()
{
    parent::_initSelect();

    $this->getSelect()->joinLeft(
        ['secondTable' => $this->getTable('admin_user')],
        'main_table.user_id = secondTable.user_id',
        ['username']
    )->joinLeft(
        ['thirdTable' => $this->getTable('catalog_product_entity')],
        'main_table.product_id = thirdTable.entity_id',
        ['sku']
    );//use fourthTable, fifthTable to join more tables
}

3# now we create Collection.php in path structure : app/code/NameSpace/ModuleName/Model/ResourceModel/ModelName/Grid

In my case, it is app/code/Thecoachsmb/Grid/Model/ResourceModel/Grid/Grid

This also means that the grid class should become a physical file. Therefore create this file in the directory specified in the di.xml file.

<?php

namespace Thecoachsmb\Grid\Model\ResourceModel\Grid\Grid;

use Magento\Framework\Api\Search\SearchResultInterface;
use Magento\Framework\Search\AggregationInterface;
// your mane table collection
use Thecoachsmb\Grid\Model\ResourceModel\Grid\Collection as GridCollection;
use Magento\Framework\Data\Collection\EntityFactoryInterface;
use Psr\Log\LoggerInterface;
use Magento\Framework\Data\Collection\Db\FetchStrategyInterface;
use Magento\Framework\Event\ManagerInterface;
use Magento\Store\Model\StoreManagerInterface;
use Magento\Framework\Model\ResourceModel\Db\AbstractDb;

/**
 * Class Collection
 * Collection for displaying grid
 */
class Collection extends GridCollection implements SearchResultInterface
{
    /**
     * Resource initialization
     * @param EntityFactoryInterface   $entityFactory,
     * @param LoggerInterface          $logger,
     * @param FetchStrategyInterface   $fetchStrategy,
     * @param ManagerInterface         $eventManager,
     * @param StoreManagerInterface    $storeManager,
     * @param String                   $mainTable,
     * @param String                   $eventPrefix,
     * @param String                   $eventObject,
     * @param String                   $resourceModel,
     * @param $model = 'Magento\Framework\View\Element\UiComponent\DataProvider\Document',
     * @param $connection = null,
     * @param AbstractDb              $resource = null
     * @return $this
     */
    public function __construct(
        EntityFactoryInterface $entityFactory,
        LoggerInterface $logger,
        FetchStrategyInterface $fetchStrategy,
        ManagerInterface $eventManager,
        StoreManagerInterface $storeManager,
        $mainTable,
        $eventPrefix,
        $eventObject,
        $resourceModel,
        $model = 'Magento\Framework\View\Element\UiComponent\DataProvider\Document',
        $connection = null,
        AbstractDb $resource = null
    ) {
        parent::__construct(
            $entityFactory,
            $logger,
            $fetchStrategy,
            $eventManager,
            $storeManager,
            $connection,
            $resource
        );
        $this->_eventPrefix = $eventPrefix;
        $this->_eventObject = $eventObject;
        $this->_init($model, $resourceModel);
        $this->setMainTable($mainTable);
    }

    /**
     * @return AggregationInterface
     */
    public function getAggregations()
    {
        return $this->aggregations;
    }

    /**
     * @param AggregationInterface $aggregations
     *
     * @return $this
     */
    public function setAggregations($aggregations)
    {
        $this->aggregations = $aggregations;
    }

    /**
     * Get search criteria.
     *
     * @return \Magento\Framework\Api\SearchCriteriaInterface|null
     */
    public function getSearchCriteria()
    {
        return null;
    }

    /**
     * Set search criteria.
     *
     * @param \Magento\Framework\Api\SearchCriteriaInterface $searchCriteria
     *
     * @return $this
     * @SuppressWarnings(PHPMD.UnusedFormalParameter)
     */
    public function setSearchCriteria(
        \Magento\Framework\Api\SearchCriteriaInterface $searchCriteria = null
    ) {
        return $this;
    }

    /**
     * Get total count.
     *
     * @return int
     */
    public function getTotalCount()
    {
        return $this->getSize();
    }

    /**
     * Set total count.
     *
     * @param int $totalCount
     *
     * @return $this
     * @SuppressWarnings(PHPMD.UnusedFormalParameter)
     */
    public function setTotalCount($totalCount)
    {
        return $this;
    }

    /**
     * Set items list.
     *
     * @param \Magento\Framework\Api\ExtensibleDataInterface[] $items
     *
     * @return $this
     * @SuppressWarnings(PHPMD.UnusedFormalParameter)
     */
    public function setItems(array $items = null)
    {
        return $this;
    }
}

# now you can add 2nd table columns in your grid file grid_record_grid_list.xml in  app/code/Thecoachsmb/Grid/view/adminhtml/ui_component folder and then 2nd table column include in grid.