selecting things that don't exist in another table

Say I had two tables and that each one had, within it, a common
primary key (stock_num).

eg.

products_in_stock:
stock_num
quantity

products_ordered_for_stock:
stock_num
quantity

I'd like to be able to select all the stock_num's in products_in_stock
that aren't in products_ordered_for_stock. Any idea as to how I might
do this?

I realize the database could redone more efficiently (maybe just
making one table with three rows - stock_num, quantity_in_stock,
quantity_ordered), but unfortunately, that's not an option.

Any ideas?

On Apr 13, 9:12 am, "yawnmoth" <terra1...@yahoo.com> wrote:
> Say I had two tables and that each one had, within it, a common
> primary key (stock_num).
>
> eg.
>
> products_in_stock:
> stock_num
> quantity
>
> products_ordered_for_stock:
> stock_num
> quantity
>
> I'd like to be able to select all the stock_num's in products_in_stock
> that aren't in products_ordered_for_stock. Any idea as to how I might
> do this?
>
> I realize the database could redone more efficiently (maybe just
> making one table with three rows - stock_num, quantity_in_stock,
> quantity_ordered), but unfortunately, that's not an option.
>
> Any ideas?

Try this:

select pi.stock_num
from products_in_stock pi
left join products_ordered_for_stock po
on pi.stock_num = po.stock_num
where po.stock_num is null;