>>I am creating a query where I can't seem to find a way to create it with one SQL Select. I thought before I give up and do it with 2 or 3 SQL selects, maybe someone can see a way.
>>
>>I have 3 tables:
>>
>>PARTS
>>PART # MODEL... etc.
>>001
>>002
>>003
>>and so on
>>
>>PARTS USED
>>PART # QTY USED DATE_USED
>>001 1 1/1/2002
>>001 3 2/2/2002
>>002 1 1/4/2002
>>and so on
>>
>>PARTS RECEIVED
>>PART # QTY REC. DATE REC.
>>001 3 1/3/2003
>>002 3 3/1/2001
>>001 4 4/12/2000
>>and so on
>>
>>I want to get a query of parts with SUM of used and SUM of received for a time period.
>>
>>So the resulting query would have the following fields
>>PART # SUM_USED SUM_RECEIVED
>>001 ## ##
>>002 ## ##
>>003 ## ##
>>
>>I tested this with one SQL select by selecting PARTS and joining PARTSUSED and PARTSRECEIVED. But then the resulting qty is greater than actual because SQL duplicates records from USED and RECEIVED tables.
>>
>>Anybody can see if it could be done with one SQL select?
>>
>>TIA.
>
>I dont know for only 1 select.
>But in 2 step:
>
>

>* put all needed info in 1 cursor
>SELECT parts.partno, 0000000000 as qtyused, 0000000000 as qtyrec FROM parts ;
>	UNION(SELECT partno, qtyused, 000000000 as qtyrec FROM USEDPARTS) ;
>	UNION(SELECT partno, 0000000000 as qtyused, qtyrec FROM RECEIVED) ;
>	INTO CURSOR dummy
>
>* calculate the sum
>SELECT partno,sum(qtyused),sum(qtyrec) from dummy GROUP BY 1
>
>

This is pretty much how I thought it could be done, with two SQL selects. I wanted to be sure I am not missing some way to pull it all into one SQL select. Not to say that 1 SQL select would always make the query faster.

Thank you for your help.