>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