>(I submitted this twice since I had forgotten to use the "pre" command to format the output below.)
>
>I am trying to write a SQL Statement that has three tables. I am looking at WIRE STANDS being received from various customers WITHOUT WIRE on them into our Company and a certain number being sent out to customers WITH WIRE on them.
>
>We have the following tables:
>
>CUSTOMERS-Holds all of our customers company names and CUST ID.
>STANDSIN - with a field called Qty for stands received from customers
>STANDSOUT- also with a field called Qty for stands shipped out
>
>Customer has a CUST field and is indexed as CUST.
>StandsIn and StandOut also have CUST as one of their related fields to the CUSTOMER table.
>
>I want to end up with a report GROUPED by CUSTOMERS showing stands Received and Stands shipped out as followed:
>
>

>NAME OF CUSTOMER QTY Recv'd QTY Sent Out Difference (Recv'd-Shipped)
>CUSTOMER_A       13         23           -10
>CUSTOMER_B       98         58           40
>CUSTOMER_C       112        140          -28
>CUSTOMER_D       1000       980          20
>

>
>What we are trying to do is to focus on the customers who have not been returning our stands, so that if we see a negative number, we know that that customer has not been returning our WIRE STANDS and can then focus on them.
>
>I tried a regular UNION SQL Statement, but that didn't seem right. Would this require more of a LEFT OUTER JOIN type of statement?

select cus.CustomerName, ;
 sum(sin.qty) as qtyRecv, ;
 sum(sout.qty) as qtySent, ;
 sum(sin.qty - sout.qty) as difference ;
 from customers cus ;
    left join standsin sin on cus.cust_id == sin.cust_id ;
    left join standsout sout on cus.cust_id == sout.cust_id ;
 group by 1 ;
 order by 4

Cetin