Joins

join.js

db.transfers.aggregate( [
    {
      $lookup:
        {
          from: "accounts",
          localField: "transfer_id",
          foreignField: "transfers_complete",
          pipeline: [
 
             { $project: { _id: 0, account_id: 1, account_holder: 1 } }
          ],
          as: "account_holder"
        }
   }
 ] )

join.sql

SELECT *, account_holder
FROM transfers
WHERE inventory_docs IN (
   SELECT account_holder, account_id
   FROM accounts
   WHERE transferss_complete = transfers.transfer_id
);