Syntax error to combine left join and select

I’m getting a syntax error at Left Join. So in trying to combine the two, i used the left join and the brackets. I’m not sure where the problem is:

SELECT DISTINCT a.order_id
FROM fact.outbound AS a
ORDER BY Rand()
LIMIT 5
  LEFT JOIN (
    SELECT
      outbound.marketplace_name,
      outbound.product_type,
      outbound.mpid,
      outbound.order_id,
      outbound.sku,
      pbdh.mpid,
      pbdh.product_type,
      pbdh.validated_exp_reach,
      pbdh.ultimate_sales_rank_de,
      pbdh.ultimate_sales_rank_fr,
      (
        pbdh.very_good_stock_count + good_stock_count + new_Stock_count
      ) as total_stock
    FROM
      fact.outbound AS outbound
      LEFT JOIN reporting_layer.pricing_bi_data_historisation AS pbdh ON outbound.mpid = pbdh.mpid
      AND trunc(outbound.ordered_date) = trunc(pbdh.importdate)
    WHERE
      outbound.ordered_date > '2022-01-01'
      AND pbdh.importdate > '2022-01-01'
    LIMIT
      5
  ) AS b ON a.orderid = b.order_id

Error:

You have an error in your SQL syntax; it seems the error is around: ‘LEFT JOIN ( SELECT outbound.marketplace_name, outbound.product_t’ at line 9

What could be the reason?

Answer

Place the first limit logic into a separate subquery, and then join the two subqueries:

SELECT DISTINCT a.order_id
FROM
(
    SELECT order_id
    FROM fact.outbound
    ORDER BY Rand()
    LIMIT 5
) a
LEFT JOIN
(
    SELECT
        outbound.marketplace_name,
        outbound.product_type,
        outbound.mpid,
        outbound.order_id,
        outbound.sku,
        pbdh.mpid,
        pbdh.product_type,
        pbdh.validated_exp_reach,
        pbdh.ultimate_sales_rank_de,
        pbdh.ultimate_sales_rank_fr,
        (pbdh.very_good_stock_count +
         good_stock_count + new_Stock_count) AS total_stock
    FROM fact.outbound AS outbound
    LEFT JOIN reporting_layer.pricing_bi_data_historisation AS pbdh
        ON outbound.mpid = pbdh.mpid AND
           TRUNC(outbound.ordered_date) = TRUNC(pbdh.importdate)
   WHERE outbound.ordered_date > '2022-01-01' AND
         pbdh.importdate > '2022-01-01'
   -- there should be an ORDER BY clause here...
   LIMIT 5
) AS b
    ON a.orderid = b.order_id;

Note that the select clause of the b subquery can be reduced to just the order_id, as no values from this subquery are actually selected in the end.