At 11:41 AM 16/08/2004 +0100, you wrote:

>Time to show my ignorance ;)
>
>I have a table with part numbers and details, and a second with batches
>of stock. So some parts will have no stock and some will have several
>batch entries.
>
>What I need to produce is a query that returns every part number and
>additional records where there are batches in stock.
>
>n1 - 0 - 0
>n2 - 23 - b1
>n2 - 23 - b2
>n3 - 0 - 0
>
>I know this is a join, but I keep going round in circles ( and helen's
>book hasn't arrived yet ;) ).
>Do I need an LEFT,RIGHT,INNER,OUTER join ?

OK, you do want an outer join. Your choices for outer joins are LEFT,
RIGHT and FULL. Here, because you want every batch (which means multiple
records returned where you have multiple batches of one part), you can make
Parts the left stream and RIGHT join to Batches; or you can make Batches
the left stream and LEFT join to Parts. In this case, choosing one over
the other will get down to performance. (You don't want an inner join,
because that would exclude the Parts that don't have Batches; and you
don't (I think) want a FULL join, since you won't have (or be interested
in) any batches that don't have corresponding parts, right?

I dunno what the number in the middle signifies - batch quantity? Assuming
so, my preference for your set would be:
SELECT
p.PartNumber,
COALESCE(b.Quantity, 0) as Qty,
COALESCE(b.BatchNumber, 0) as BatchNo
from Batch b
LEFT JOIN Part p
on p.PartNumber = b.PartNumber
ORDER BY 1, 2 desc

/heLen