Q: Nested/subquery select in a join query I have this working SQL query :
select prd_brand.* from prd_brand
inner join
(
    select distinct value from catalog_product_entity_int
    where 
    rowid in (select rowid from catalog_product_entity_int where attribute_id = 97 and value = 1)
)t
on prd_brand.brand_id = t.value

How can I nest this select query in the join in Magento 2 ?
I did it like this :
$subquery = new \Zend_Db_Expr('SELECT DISTINCT value from catalog_product_entity_int where  rowid in (select rowid from catalog_product_entity_int where attribute_id = 97 and value = 1)');
    $brandCollection->join(array(
                        'cpei' => 'catalog_product_entity_int'),
                        'cpei.value = main_table.brand_id',
                    array('subquery' => $subquery));

But it gives this query :
    SELECT `main_table`.*, 
SELECT DISTINCT value from catalog_product_entity_int 
where rowid in 
(select rowid from catalog_product_entity_int where attribute_id = 97 and value = 1) AS `subquery` 
FROM `prd_brand` AS `main_table` 
INNER JOIN `catalog_product_entity_int` AS `cpei` ON cpei.value = main_table.brand_id


A: Can you try like below. It will surely work. i have checked in my local :).
$subquery = new \Zend_Db_Expr('(SELECT DISTINCT value from catalog_product_entity_int where  row_id in (select row_id from catalog_product_entity_int where attribute_id = 97 and value = 1))');

$brandCollection->getSelect()->join( array( 't' => $subquery ), 'main_table.brand_id = t.value', array());


A: For what it's worth I've implemented this way. It works on Magento >= 2.3.
# use Magento\Sales\Api\Data\OrderInterface;
# use Magento\Sales\Api\Data\OrderInterfaceFactory;
# use Magento\Framework\App\ResourceConnection;

    public function __construct(
        ...
        ResourceConnection $resource,
        OrderCollectionFactory $orderCollectionFactory,
        OrderInterfaceFactory $orderInterfaceFactory
    ) {
        ...
        $this->resource = $resource;
        $this->orderCollectionFactory = $orderCollectionFactory;
        $this->orderInterfaceFactory = $orderInterfaceFactory;
    }

       $salesOrderTable = $this->getResource()->getTableName('sales_order');
        $salesOrderItemTable = $this->getResource()->getTableName('sales_order_item');

        $lastOrder = $this->orderInterfaceFactory->create()
            ->loadByIncrementId($orderIncrementId);

        if(!$lastOrder->getId()) {
            return false;
        }

        $orderCollection = $this->orderCollectionFactory->create();

        $subQuery = new \Zend_Db_Expr(sprintf("(SELECT created_at FROM %s WHERE increment_id = '%s')",
            $salesOrderTable,
            $orderIncrementId));

        $orderCollection->getSelect()
            ->join(
                ['sales_item' => $salesOrderItemTable],
                'main_table.entity_id = sales_item.order_id'
            );

        $orderCollection->addFieldToFilter('main_table.created_at', [
                'gt' => $subQuery,
                ])
            ->addFieldToFilter('main_table.state', ['in' => [
                Order::STATE_NEW,
                Order::STATE_PROCESSING,
                Order::STATE_PAYMENT_REVIEW,
                Order::STATE_PENDING_PAYMENT,
                Order::STATE_HOLDED,
                Order::STATE_COMPLETE,
            ]])
            ->addFieldToFilter('sales_item.sku', $sku);

        $orderCollection->getSelect()
            ->reset(\Zend_Db_Select::COLUMNS)
            ->columns(['SUM(sales_item.qty_ordered - sales_item.qty_refunded) AS sold']);

    if(!$orderCollection->getSize()) {
        return false;
    }

    return $orderCollection->getFirstItem()->getData('sold');

