Subject | LEFT JOIN HELP |
---|---|
Author | dr_john_mp |
Post date | 2007-02-14T12:14:57Z |
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)?
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)?