Select statement help

From: AM <nospam_at_man.com>
Date: Fri, 22 Nov 2002 13:40:43 -0400
Message-ID: <6_tD9.208332$C8.527646@nnrp1.uunet.ca>

Hi,

I have two tables; INVENTORY and SALES. I am trying to come up with a select statement for a report that will show all inventory records. Along with that, also include quantity from the sales table. If there is no sales, have a zero placed in SAL_QTY field. If there is a record, populate SAL_QTY and calculate and populate ON_HAND field.

Here is what I got so far:

 SELECT INVENTORY.*,
    INVENTORY.INV_QTY - SALES.SAL_QTY AS ONHAND,     SALES.SAL_QTY AS Expr1
    FROM INVENTORY, SALES
    WHERE INVENTORY.INV_ITEM = SALES.SAL_ITEM The problem is that only records found with matching item numbers are captured. I want all inventory items whether or not they have sales associated with them displayed. Here are the column names:

SAL_ITEM
SAL_QTY
SAL_CUST

INV_ITEM
INV_DESC
INV_TYPE
INV_QTY

INV_ORDER
INV_PRICE Here are my fields for my report.
INV_ITEM INV_DESC INV_TYPE INV_QTY INV_PRICE INV_ORDER SAL_QTY ON_HAND Is this even possible??? Any help would be great!

Thanks Received on Fri Nov 22 2002 - 11:40:43 CST