I think there are several ways to solve your problem, one of them are

Select s.stock_code,o.orderno,o.duedate
from shortages s
left join orders o on s.stock_code=o.stock_code
and not exists(select * from orders o2
where s.stock_code = o2.stock_code
and o2.duedate < o.duedate)

HTH,
Set

dr_john_mp wrote:

> Some help on a left join please.
>
> I have a table of shortages , and a table of outstanding orders.
>
> I can do a left join to list the shortages and show outstanding orders
> where they exist - simplifying
>
> Select s.stock_code,o.orderno,o.duedate
> from shortages s left join orders o on s.stock_code=o.stock_code
>
> The only issue with this is that if there are several outstanding
> orders for a specific part I get one line/record for each order -
> which is confusing some users.
>
> Is there a way to restrict the join to only one match (eg the order
> with the lowest duedate)?