Skip to main content
2 of 3
added 3 characters in body
McNets
  • 23.9k
  • 11
  • 50
  • 89

If you need to limit the number of rows returned by one of your tables, you could use a CROSS APPLY solution.

Right table expression is processed once for each row from the left table expression.

CROSS APPLY (SELECT TOP 1 *
             FROM   skh.v_list_price_each
             WHERE  skh.v_price_book.item_short = skh.v_list_price_each.item_short) lpe

Due you're using a TOP (X) expression, you could add an ORDER BY clause into the SELECT statement.

SELECT      skh.ARCUS.IDCUST,
            skh.ICITEM.ITEMNO AS product_code,
            skh.ICITEM.CNTLACCT,
            lpe.price AS list_price,
            skh.v_price_book.price,
            lpe.unit AS Expr2,
            skh.v_price_book.price_unit
FROM        skh.ARCUS
INNER JOIN  skh.v_price_book 
ON          skh.ARCUS.PRICLIST = skh.v_price_book.price_list
INNER JOIN  skh.ICITEM 
ON          skh.v_price_book.item_short = skh.ICITEM.ITEMNO
AND         skh.v_price_book.company = skh.ICITEM.AUDTORG
CROSS APPLY (SELECT TOP 1 *
             FROM   skh.v_list_price_each
             WHERE  skh.v_price_book.item_short = skh.v_list_price_each.item_short) lpe
WHERE       skh.ICITEM.INACTIVE = 0
AND         skh.ARCUS.SWACTV = 1
ORDER BY    skh.ARCUS.IDCUST;
McNets
  • 23.9k
  • 11
  • 50
  • 89