Re: Select statement help

Two things.

1. In order to get inventory rows which have no matching sales rows you need to do an outer join, which is accomplished by using (+) in your where clause: where inventory.inv_item = sales.item (+)
2. Since the SALES table contains the column SAL_CUST I assume there will be multiple rows with the same INV_ITEM whenever the same item is sold to multiple customers. In that case you will need to use the SUM function on your sales columns: SUM(sales.sal_qty)

"AM" <nospam_at_man.com> wrote in
news:6_tD9.208332$C8.527646_at_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

-- 
Ken Denny
http://www.kendenny.com/