Thanks

--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@...> wrote:

>
> 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)?
>