"Joel" <jo**@ecsnj.com> wrote in message
news:mKfyd.7335$152.4970@trndny01...
Hi,
Table Oelsls contains all products bought (history) table Invt
contains the products to sell (Inventory) I'm trying with no success to list all
products in Oelsls table (field name in both tables: part_id) that are not
in the Invt table.
My objective is a grid of all Special (non inventory) products.
Can anyone produce the correct sql statement?
Thank you.
Depends of the version of MySQL.
If you don't have subqueries then
create table BoughtItems(
ItemNumber tinyint unsigned not null primary key);
create table InventoryItems(
ItemNumber tinyint unsigned not null primary key);
insert into BoughtItems (ItemNumber)values(1),(2),(3),(10);
insert into InventoryItems(ItemNumber)values(1),(2),(3)
SELECT BoughtItems.ItemNumber FROM BoughtItems
LEFT OUTER JOIN InventoryItems on InventoryItems.ItemNumber =
BoughtItems.ItemNumber
where InventoryItems.ItemNumber is null;
If you do have subqueries, then:
SELECT ItemNumber FROM BoughtItems where ItemNumber not in(
SELECT ItemNumber FROM InventoryItems)
Regards,
Rich